package com.mzj.saas.mservice.community.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.mzj.saas.commons.*;
import com.mzj.saas.commons.enums.BacklogItemTypeEnum;
import com.mzj.saas.commons.enums.HouseAuditStatusEnum;
import com.mzj.saas.commons.enums.HouseBindStatusEnum;
import com.mzj.saas.commons.enums.HouseMatchStatusEnum;
import com.mzj.saas.commons.vo.redis.TokenRedisVo;
import com.mzj.saas.mservice.area.entity.Area;
import com.mzj.saas.mservice.area.repository.AreaRepository;
import com.mzj.saas.mservice.backlog.entity.BacklogItem;
import com.mzj.saas.mservice.backlog.repository.BacklogItemRepository;
import com.mzj.saas.mservice.bill.entity.HouseExpenseHouse;
import com.mzj.saas.mservice.bill.entity.HouseExpenseRent;
import com.mzj.saas.mservice.bill.repository.HouseExpenseHouseRepository;
import com.mzj.saas.mservice.bill.repository.HouseExpenseRentRepository;
import com.mzj.saas.mservice.community.entity.*;
import com.mzj.saas.mservice.community.repository.*;
import com.mzj.saas.mservice.community.vo.*;
import com.mzj.saas.mservice.department.entity.Department;
import com.mzj.saas.mservice.department.entity.UserDepartmentRef;
import com.mzj.saas.mservice.department.repository.DepartmentRepository;
import com.mzj.saas.mservice.department.repository.UserDepartmentRefRepository;
import com.mzj.saas.mservice.eligible.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.mservice.eligible.entity.DepartmentHierarchy;
import com.mzj.saas.mservice.eligible.repository.DepartmentEnterpriseTagRefRepository;
import com.mzj.saas.mservice.eligible.repository.DepartmentHierarchyRepository;
import com.mzj.saas.mservice.house.entity.House;
import com.mzj.saas.mservice.house.repository.HouseRepository;
import com.mzj.saas.mservice.house.service.HouseService;
import com.mzj.saas.mservice.house.vo.HouseVO;
import com.mzj.saas.mservice.house.vo.OrgVO;
import com.mzj.saas.mservice.sys.entity.User;
import com.mzj.saas.mservice.sys.repository.UserRepository;
import com.mzj.saas.mservice.sys.utils.SqlUtils;
import com.mzj.saas.redis.RedisService;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Service
@Transactional
public class CommunityService {
    private static final Logger log = LoggerFactory.getLogger(CommunityService.class);

    @Value("${contract.tmp.dir}")
    private String contractTmpDir;
    @Autowired
    private RedisService redisService;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityBuildUnitRepository communityBuildUnitRepository;
    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private HouseService houseService;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private AreaRepository areaRepository;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private CommunityPublicFacilitiesRepository communityPublicFacilitiesRepository;
    @Autowired
    private CommunityBuildingOntologyRepository communityBuildingOntologyRepository;
    @Autowired
    private CommunitySafetyFacilitiesRepository communitySafetyFacilitiesRepository;
    @Autowired
    private CommunityServiceFacilitiesRepository communityServiceFacilitiesRepository;
    @Autowired
    private AppCommunityService appCommunityService;
    @Autowired
    private AppCommunityHouseService appCommunityHouseService;
    @Autowired
    @Qualifier("stringRedisTemplate")
    private StringRedisTemplate redisTemplate;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private BacklogItemRepository backlogItemRepository;
    @Autowired
    private HouseExpenseRentRepository houseExpenseRentRepository;
    @Autowired
    private HouseExpenseHouseRepository houseExpenseHouseRepository;
    @Autowired
    private HouseTypeRepository houseTypeRepository;
    @Autowired
    private UserDepartmentRefRepository userDepartmentRefRepository;
    /**
     * 查询小区,实现了根据不同的用户查询出用户下面对应的小区数据
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<List<Map<String, Object>>> findCommunity(String accessToken) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取用户ID
            String userId = tokenRedisVo.getId();
            //获取用户登录类型：1企业用户登录 2普通用户登录
            Integer loginType = tokenRedisVo.getLoginType();
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();

            List<Map<String, Object>> list = new ArrayList<>();
            //如果是企业用户登录的话,走下面的逻辑,实现了查询出企业下的小区数据
            if (loginType == 1) {
                //根据用户ID查询出用户层级权限
                DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
                if (hierarchy == null) {
                    return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
                }
                //获取机构ID
                Long id = hierarchy.getId();
                //根据机构ID查询机构消息
                List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
                //遍历获取企业标签集合
                List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

                //1、政府机构，根据区域标签所对应区域id查所有小区
                Long areaId = null;
                String areaTag = "province_id";
                //判读区域标签所对应区域的id查公租房
                if (enterpriseTagIds.contains(3L)) {
                    Long areaTagId = hierarchy.getTagId();//区域标签
                    if (areaTagId == null) {
                        return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
                    }
                    // 政府机构，根据区域标签所对应区域id查公租房
                    if (areaTagId == 1) {
                        areaTag = "province_id";
                    } else if (areaTagId == 2) {
                        areaTag = "city_id";
                    } else if (areaTagId == 3) {
                        areaTag = "district_id";
                    } else if (areaTagId == 4) {
                        areaTag = "street_id";
                    } else if (areaTagId == 5) {
                        areaTag = "community_id";
                    }
                    areaId = hierarchy.getAreaId();//区域id
                }

                //2、产权单位，根据orgId查公租房所对应小区
                List<String> rcIds = new ArrayList<>();
                //判断机构标签所对应区域的id查公租房
                if (enterpriseTagIds.contains(2L)) {
                    List<House> houses = houseRepository.findByOrgId(enterpriseId);
                    //存储机构对应区域的公租房数据
                    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                }

                //3、物业，查已绑定该物业的小区
                Long hierarchyOrgId = null;
                if (enterpriseTagIds.contains(4L)) {
                    //判读小区标签所对应区域的id查公租房
                    hierarchyOrgId = hierarchy.getOrgId();
                }
                //4、单位承租人，查已绑定该物业的小区
                if (enterpriseTagIds.contains(7L)) {
                    List<House> houses = houseRepository.findByUnitLesseeIdAndStatus(enterpriseId,1);
                    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                }
                //5、房东，查已绑定该物业的小区
                if (enterpriseTagIds.contains(8L)) {
                    List<House> houses = houseRepository.findByOrgIdAndStatus(enterpriseId,1);
                    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                }
                //拼接区域为公租房的数据
                StringBuilder where = new StringBuilder();
                List<Object> args = new ArrayList<>();
                where.append(" where 1 = 1");
                where.append(" and (c." + areaTag + " = ? or c.org_id = ? or c.id in ");
                args.add(areaId);
                args.add(hierarchyOrgId);
                //如果机构区域的公租房有数据
                if (rcIds != null && !rcIds.isEmpty()) {
                    where.append(SqlUtils.foreachIn(rcIds.size()));
                    args.addAll(rcIds);
                } else {
                    where.append("(null)");
                }
                where.append(")");
                //查询出机构区域对应的公租房数据
                list = jdbcTemplate.queryForList("SELECT c.id AS communityId,c.name AS communityName,c.address FROM `saas_community` c"
                        + where + " AND c.status = 1", args.toArray());
            }
            //如果是普通用户登录的话,走下面的逻辑,实现了查询出普通用户下的小区数据
            if (loginType == 2) {
                //根据用户ID查询出公租房数据
                List<String> houseIdList = getHouseIdList(userId);
                //如果公租房数据不为空
                if (!houseIdList.isEmpty()) {
                    String sql = "SELECT c.id AS communityId,c.name AS communityName,c.address \n" +
                            "FROM saas_house h LEFT JOIN `saas_community` c ON h.residence_community_id = c.id\n" +
                            "WHERE h.id IN " + SqlUtils.foreachIn(houseIdList.size());
                    list = jdbcTemplate.queryForList(sql, houseIdList.toArray());
                }
            }
            //遍历存储普通用户公租房数据
            list = list.stream().collect(
                    Collectors.collectingAndThen(
                            Collectors.toCollection(
                                    () -> new TreeSet<>(Comparator.comparing(m -> m.get("communityId").toString()))
                            ), ArrayList::new
                    )
            );
            //把数据传输给前端页面
            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            //有异常打印日志
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 公租房承租人、公租房同住人、房产租客成员，房屋信息查询
     */
    public List<String> getHouseIdList(String userId) {
        /**
         * 查询当前登录人名下的房屋id
         */
        String houseSql = "SELECT u.house_id AS houseId \n" +
                "FROM saas_house_match_user u \n" +
                "LEFT JOIN `saas_house` h ON u.house_id = h.`id`\n" +
                "WHERE u.match_user_id = ? AND u.status = 1 AND h.`lease_status` = 1\n" +
                "UNION \n" +
                "SELECT u.house_id AS houseId \n" +
                "FROM saas_house_match_user u\n" +
                "LEFT JOIN `saas_house` h ON u.house_id = h.`id` \n" +
                "LEFT JOIN (SELECT c.apply_id, us.id, us.enterprise_user_id FROM saas_house_eligible_apply_cohabit c LEFT JOIN saas_app_user us ON us.card_number = c.card_number) c1 \n" +
                "ON u.apply_id = c1.apply_id \n" +
                "WHERE c1.id = ? OR c1.enterprise_user_id = ? AND u.status = 1 AND h.`lease_status` = 1\n" +
                "UNION \n" +
                "SELECT u2.community_house_id AS houseId \n" +
                "FROM saas_community_house_user u2\n" +
                "LEFT JOIN `saas_house` h ON u2.community_house_id = h.`id` \n" +
                "WHERE u2.user_id = ? AND u2.status = 1";
        return jdbcTemplate.query(houseSql, new SingleColumnRowMapper<>(String.class), userId, userId, userId, userId);
    }

    public ResultBean<List<Map<String, Object>>> findBuild(String communityId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            List<CommunityBuild> builds = communityBuildRepository.findByCommunityIdOrderByName(communityId);
            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuild build : builds) {
                Map<String, Object> map = new HashMap<>();
                map.put("buildId", build.getId());
                map.put("buildName", build.getName());
                list.add(map);
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<List<Map<String, Object>>> findHouseBuild(String communityId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            String sql = "SELECT DISTINCT h.building_num AS buildId,b.name AS buildName \n" +
                    "FROM saas_house h LEFT JOIN saas_community_build b ON h.building_num = b.id\n" +
                    "WHERE h.residence_community_id = ? AND h.house_nature = 1   ";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, communityId);

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<List<Map<String, Object>>> findUnit(String communityId, Long buildId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }
            if (buildId == null) {
                return ResultBean.failedResultOfParamWithMsg("楼栋id为空");
            }

            List<CommunityBuildUnit> units = communityBuildUnitRepository.findByCommunityIdAndBuildIdOrderByName(communityId, buildId);
            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuildUnit unit : units) {
                Map<String, Object> map = new HashMap<>();
                map.put("unitId", unit.getId());
                map.put("unitName", unit.getName());
                list.add(map);
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<List<String>> findRoomNumber(String communityId, Long buildId, Long unitId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }
            if (buildId == null) {
                return ResultBean.failedResultOfParamWithMsg("楼栋id为空");
            }
            if (unitId == null) {
                return ResultBean.failedResultOfParamWithMsg("单元id为空");
            }

            //houseNature; // 性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
            List<Integer> houseNatureList = new ArrayList<>();
            houseNatureList.add(1);
            List<House> houses = houseRepository.findByResidenceCommunityIdAndBuildingNumAndUnitAndHouseNatureIn(communityId, buildId, unitId, houseNatureList);
            List<String> list = new ArrayList<>();
            for (House house : houses) {
                list.add(house.getRoomNumber());
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }


    public ResultBean<List<String>> findRoomNumberAll(String communityId, Long buildId, Long unitId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }
            if (buildId == null) {
                return ResultBean.failedResultOfParamWithMsg("楼栋id为空");
            }
            if (unitId == null) {
                return ResultBean.failedResultOfParamWithMsg("单元id为空");
            }

            List<House> houses = houseRepository.findByResidenceCommunityIdAndBuildingNumAndUnit(communityId, buildId, unitId);
            List<String> list = new ArrayList<>();
            for (House house : houses) {
                list.add(house.getRoomNumber());
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<List<Map<String, Object>>> findBuildUnitRoomNumber(String communityId, Integer chargeType, Integer chargeMode) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            List<CommunityBuild> builds = communityBuildRepository.findByCommunityId(communityId);

            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuild build : builds) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", build.getId());
                map.put("name", build.getName());

                List<Map<String, Object>> unit = findUnit(communityId, build.getId()).getResultData();
                List<Map<String, Object>> children = new ArrayList<>();
                for (Map<String, Object> u : unit) {
                    Map<String, Object> map1 = new HashMap<>();
                    Long unitId = (Long) u.get("unitId");
                    //houseNature; // 性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
                    List<Integer> houseNatureList = new ArrayList<>();
                    houseNatureList.add(1);
                    List<House> houses = houseRepository.findByResidenceCommunityIdAndBuildingNumAndUnitAndHouseNatureIn(communityId, build.getId(), unitId, houseNatureList);
                    List<Map<String, Object>> children1 = new ArrayList<>();

                    map1.put("id", u.get("unitId"));
                    map1.put("name", u.get("unitName"));
                    for (House h : houses) {
                        Map<String, Object> map2 = new HashMap<>();

                        if (chargeType != null) {
                            if (chargeType.equals(3) || chargeType.equals(4) || chargeType.equals(5) || chargeType.equals(7)) {

                                //计费方式:1按月 2按季 3按半年 4按年
                                //查询房产-收费项目-其他费用账单  saas_house_expense_other
                                String SQL = " SELECT o.charge_mode AS chargeMode FROM saas_house_expense_other o INNER JOIN saas_house_expense_house h ON o.id=h.expense_id "
                                        + "  WHERE h.house_id = ? AND o.charge_type = ? AND o.enable_status = 1 AND o.status = 1 ";

                                List<Object> args = new ArrayList<>();
                                args.add(h.getId());
                                args.add(chargeType);
                                List<Map<String, Object>> expenseOtherList = jdbcTemplate.queryForList(SQL, args.toArray());
                                if (expenseOtherList.size() > 0) {
                                    Integer c = (Integer) expenseOtherList.get(0).get("chargeMode");
                                    if (!chargeMode.equals(c)) {
                                        continue;  //计费方式不等，无需返回此房子
                                    }
                                } else {
                                    //没有计费方式则无需返回此房子
                                    continue;
                                }

                            }
                        }
                        map2.put("houseNature", h.getHouseNature());
                        map2.put("id", h.getId());
                        map2.put("name", h.getRoomNumber());
                        map2.put("unitId", map1.get("id"));
                        map2.put("unitName", map1.get("name"));
                        map2.put("buildId", map.get("id"));
                        map2.put("buildName", map.get("name"));
                        children1.add(map2);
                    }
                    map1.put("children", children1);
                    children.add(map1);
                }
                map.put("children", children);

                list.add(map);
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    //查询楼单元物业房产
    public ResultBean<List<Map<String, Object>>> findCommunityHouse(String communityId, String expenseId, Integer chargeType, Integer chargeMode) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            List<CommunityBuild> builds = communityBuildRepository.findByCommunityId(communityId);

            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuild build : builds) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", build.getId());
                map.put("name", build.getName());

                List<Map<String, Object>> unit = findUnit(communityId, build.getId()).getResultData();
                List<Map<String, Object>> children = new ArrayList<>();
                for (Map<String, Object> u : unit) {
                    Map<String, Object> map1 = new HashMap<>();
                    Long unitId = (Long) u.get("unitId");
                    //houseNature; // 性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
                    List<Integer> houseNatureList = new ArrayList<>();
                    houseNatureList.add(1);
                    houseNatureList.add(2);
                    houseNatureList.add(3);
                    houseNatureList.add(4);
                    houseNatureList.add(5);
                    List<House> houseList = houseRepository.findByResidenceCommunityIdAndBuildingNumAndUnitAndHouseNatureIn(communityId, build.getId(), unitId, houseNatureList);
                    List<Map<String, Object>> children1 = new ArrayList<>();

                    map1.put("id", u.get("unitId"));
                    map1.put("name", u.get("unitName"));
                    for (House h : houseList) {
                        Map<String, Object> map2 = new HashMap<>();

                        if (chargeType != null) {
                            //chargeType 收费项目(1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费 8租金 9押金)
                            if (chargeType.equals(8)) {
                                chargeType = 1;
                                //查询收费项目-房租/押金账单
                                List<HouseExpenseRent> houseExpenseRentList = houseExpenseRentRepository.findByHouseIdAndChargeTypeAndEnableStatusAndStatus(h.getId(), chargeType, 1, 1);
                                if (houseExpenseRentList.size() > 0) {
                                    //计费方式:1按月 2按季 3按半年 4按年
                                    if (!chargeMode.equals(houseExpenseRentList.get(0).getChargeMode())) {
                                        continue;  //计费方式不等，无需返回此房子
                                    }
                                } else {
                                    //没有计费方式则无需返回此房子
                                    continue;
                                }
                            }
                            if (chargeType.equals(3) || chargeType.equals(4) || chargeType.equals(5) || chargeType.equals(7)) {

                                //计费方式:1按月 2按季 3按半年 4按年
                                //查询物业房产-收费项目-其他费用账单  saas_house_expense_other
                                String SQL = " SELECT o.charge_mode AS chargeMode FROM saas_house_expense_other o INNER JOIN saas_house_expense_house h ON o.id=h.expense_id "
                                        + "  WHERE h.house_id = ? AND o.charge_type = ? AND o.enable_status = 1 AND o.status = 1 ";

                                List<Object> args = new ArrayList<>();
                                args.add(h.getId());
                                args.add(chargeType);
                                List<Map<String, Object>> expenseOtherList = jdbcTemplate.queryForList(SQL, args.toArray());
                                if (expenseOtherList.size() > 0) {
                                    Integer c = (Integer) expenseOtherList.get(0).get("chargeMode");
                                    if (!chargeMode.equals(c)) {
                                        continue;  //计费方式不等，无需返回此房子
                                    }
                                } else {
                                    //没有计费方式则无需返回此房子
                                    continue;
                                }

                            }
                        }

                        if (StringUtils.isNotBlank(expenseId)) {
                            //此类型已存在的房屋  [物业房产-其它费用与房产关系表]
                            List<HouseExpenseHouse> expenseHouseList = houseExpenseHouseRepository.findByExpenseId(expenseId);
                            if (expenseHouseList.size() > 0) {
                                map2.put("houseIdList", expenseHouseList.stream().map(HouseExpenseHouse::getHouseId).collect(Collectors.toList()));
                            } else {
                                map2.put("houseIdList", null);
                            }
                        }

                        map2.put("houseNature", h.getHouseNature());
                        map2.put("id", h.getId());
                        map2.put("name", h.getRoomNumber());
                        map2.put("unitId", map1.get("id"));
                        map2.put("unitName", map1.get("name"));
                        map2.put("buildId", map.get("id"));
                        map2.put("buildName", map.get("name"));
                        children1.add(map2);
                    }
                    map1.put("children", children1);
                    children.add(map1);
                }
                map.put("children", children);

                list.add(map);
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }


    /**
     * 公租房房源录入--导入,实现公租房房源录入--导入
     *
     * @param accessToken          权限鉴定
     * @param mfile                公租房房源录入的文件
     * @param residenceCommunityId 公租房房源小区ID
     * @return
     */
    public ResultBean<Object> houseImport(String accessToken, MultipartFile mfile, String residenceCommunityId) {
        //日志记录
        log.info("communityService   houseImport  accessToken ={} ", accessToken);
        //获取用户登录权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

        List<String> runPositionLog = new ArrayList<>();

        try {

            InputStream in = null;
            //判断小区ID是否为空
            if (StringUtils.isBlank(residenceCommunityId)) {
                return ResultBean.failedResultWithMsg("小区为空");
            }
            //判断文件是否存在
            if (mfile.isEmpty()) {
                return ResultBean.failedResultWithMsg("文件不存在！");
            }
            in = mfile.getInputStream();
            //使用文件输入流工具
            ImportExcelUtil importExcelUtil = new ImportExcelUtil();
            // 创建Excel工作薄
            Workbook work = importExcelUtil.getWorkbook(in, mfile.getOriginalFilename());
            if (null == work) {
                throw new Exception("创建Excel工作薄为空！");
            }
            //设置参数
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            //公租房
            List<House> houseList = new ArrayList<>();
            //去重验证map
            Map<String, Object> hashMap = new HashMap<>();

            // 遍历Excel中第一个sheet  【work.getNumberOfSheets()】
            for (int i = 0; i < 1; i++) {
                sheet = work.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                Cell c = sheet.getRow(0).getCell(8);
                String str = importExcelUtil.getCellValue(c).toString();
                if (!str.equals("产权所有人")) {
                    return ResultBean.failedResultWithMsg("请导入正确的公租房信息模板");
                }

                // 遍历当前sheet中的所有行，从第5行开始读取
                for (int j = 4; j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if (row == null || row.getFirstCellNum() == j) {
                        continue;
                    }
                    //数据组装
                    House house = new House();

                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        runPositionLog.add("【行列->值： " + (j + 1) + "," + (y + 1) + "-> " + cell + "  】");
                        Object cellValue = (cell == null ? cell : importExcelUtil.getCellValue(cell));

                        if (cellValue == null) {
                            log.error("单元格数据有空值 | 行列：" + j + "," + y);
                            return ResultBean.failedResultWithMsg("单元格数据有空值 | 行列：" + (j + 1) + "," + (y + 1));
                        }

                        //楼层值
                        Object temp = importExcelUtil.getCellValue(row.getCell(2));
                        int tempValue = Integer.valueOf((String) temp);
                        //新增的楼栋的默认层数：楼层32以内按32默认，超过按他填的
                        Integer number = 0;

                        switch (y) {
                            case 0:
                                //楼栋（栋）
                                List<CommunityBuild> list = communityBuildRepository.findByCommunityIdAndName(residenceCommunityId, (String) cellValue);
                                CommunityBuild save = new CommunityBuild();
                                if (list.size() == 0) {
                                    CommunityBuild communityBuild = new CommunityBuild();
                                    communityBuild.setCommunityId(residenceCommunityId);
                                    communityBuild.setName((String) cellValue);
                                    String value = (String) importExcelUtil.getCellValue(row.getCell(9));
                                    number = Integer.valueOf(value);
                                    communityBuild.setHouseNumber(number);
                                    switch ((String) importExcelUtil.getCellValue(row.getCell(10))) {    // 下拉选择： 居民住宅/商业/商住两用 --> 1,2,3
                                        case "居民住宅":
                                            communityBuild.setType(1);
                                            break;
                                        case "商业":
                                            communityBuild.setType(2);
                                            break;
                                        case "商住两用":
                                            communityBuild.setType(3);
                                            break;
                                    }
                                    communityBuild.setOperationId(tokenRedisVo.getId());
                                    communityBuild.setCreateTime(new Date());
                                    save = communityBuildRepository.save(communityBuild);
                                } else {
                                    save = list.get(0);
                                }

                                house.setBuildingNum(save.getId());
                                break;
                            case 1:
                                //单元
                                List<CommunityBuild> buildList = communityBuildRepository.findByCommunityIdAndName(residenceCommunityId, (String) importExcelUtil.getCellValue(row.getCell(0)));
                                List<CommunityBuildUnit> unitList = communityBuildUnitRepository.findByBuildIdAndName(buildList.get(0).getId(), (String) cellValue);
                                CommunityBuildUnit unitSave = new CommunityBuildUnit();
                                if (unitList.size() == 0) {
                                    CommunityBuildUnit communityBuildUnit = new CommunityBuildUnit();
                                    communityBuildUnit.setCommunityId(residenceCommunityId);
                                    communityBuildUnit.setBuildId(buildList.get(0).getId());
                                    communityBuildUnit.setName((String) cellValue);
                                    communityBuildUnit.setOperationId(tokenRedisVo.getId());
                                    communityBuildUnit.setCreateTime(new Date());
                                    unitSave = communityBuildUnitRepository.save(communityBuildUnit);
                                } else {
                                    unitSave = unitList.get(0);
                                }

                                house.setUnit(unitSave.getId());
                                break;
                            case 2:
                                //楼层
                                house.setFloor(Integer.valueOf((String) cellValue));
                                break;
                            case 3:
                                //房号
                                //同一个小区中，不能出现同一个楼宇单元的房产
                                List<CommunityBuild> builds = communityBuildRepository.findByCommunityIdAndName(residenceCommunityId, (String) importExcelUtil.getCellValue(row.getCell(0)));
                                List<CommunityBuildUnit> units = communityBuildUnitRepository.findByCommunityIdAndBuildIdAndName(residenceCommunityId, builds.get(0).getId(), (String) importExcelUtil.getCellValue(row.getCell(1)));
                                List<House> entityList = houseRepository.findByUnitAndRoomNumber(units.get(0).getId(), (String) cellValue);
                                if (entityList.size() > 0) {
                                    log.error("同一个小区中，不能出现同一个楼宇单元的房产 | 行列：" + j + "," + y);
                                    return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现同一个楼宇单元的房产 | 行列：" + (j + 1) + "," + (y + 1));
                                }
                                house.setRoomNumber((String) cellValue);
                                break;
                            case 4:
                                //面积（㎡）
                                Double tem = cell.getNumericCellValue();
                                house.setArea(new BigDecimal(tem));
                                break;
                            case 5:
                                //室
                                house.setRoom(Integer.valueOf((String) cellValue));
                                break;
                            case 6:
                                //厅
                                house.setHall(Integer.valueOf((String) cellValue));
                                break;
                            case 7:
                                //卫
                                house.setToilet(Integer.valueOf((String) cellValue));
                                break;
                            case 8:
                                //产权所有人
                                DepartmentHierarchy departmentHierarchy = departmentHierarchyRepository.findByNameAndStatus((String) cellValue, 1);
                                if (departmentHierarchy == null) {
                                    log.error("产权所有人为空 | 行列：" + j + "," + y);
                                    return ResultBean.failedResultOfParamWithMsg("查询不到产权所有人数据 | 行列：" + (j + 1) + "," + (y + 1));
                                }


                                house.setOrgId(departmentHierarchy.getOrgId());
                                break;
                        }
                    }
                    //根据社区ID查询数据
                    Optional<Community> communityOptional = communityRepository.findById(residenceCommunityId);
                    //获取数据
                    Community community = communityOptional.get();

                    //绑定审批状态
                    Map<String, String> map = findEnterpriseAuditStatus(community.getProvinceId(), community.getCityId(), community.getDistrictId(), community.getStreetId(), community.getCommunityId());
                    String status = map.get("status");
                    //根据录入的省份获取省份名字
                    Area area = areaRepository.findByIdAndLevel(community.getProvinceId(), 1);
                    String province = pinyin(area.getName());//省份3个首字母

                    //拼接公租房ID
                    String newId = province + System.currentTimeMillis() + RandomUtils.nextInt(1000, 10000);//省份3位首字母 + 10数字
                    house.setId(newId);
                    //获取产权人ID
                    Long orgId = house.getOrgId();
                    //绑定产权单位
                    if (orgId != null) {
                        house.setOrgId(orgId);
                        if (status.equals("0")) {//不需要审批
                            house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
                            house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
                        } else {//需要审批
                            house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
                            house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                        }
                    } else {
                        house.setBindStatus(HouseBindStatusEnum.UNBIND.getValue());
                        house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                    }
                    //根据录入的信息存储公租房
                    house.setProvinceId(community.getProvinceId());
                    house.setCityId(community.getCityId());
                    house.setDistrictId(community.getDistrictId());
                    house.setStreetId(community.getStreetId());
                    house.setCommunityId(community.getCommunityId());
                    house.setHousePropertyId(community.getOrgId()); // 所属物业
                    house.setResidenceCommunityName(community.getName());
                    house.setAddress(community.getAddress());
                    house.setResidenceCommunityId(residenceCommunityId);
                    house.setHouseNature(1);
                    house.setMatchStatus(HouseMatchStatusEnum.NOT_RENT.getValue());
                    house.setLeaseStatus(0);
                    house.setContractStatus(0);
                    house.setRecordStatus(0);
                    house.setPwdLockStatus(0);
                    house.setCheckStatus(0);
                    house.setRecordChangeStatus(0);
                    house.setOperationId(tokenRedisVo.getId());
                    house.setCreateTime(new Date());
                    //加入集合
                    houseList.add(house);
                    //去重,根据小区和楼宇单元判断输入的信息是否重复,如果重复不给予录入
                    if (!hashMap.isEmpty()) {  // Unit And
                        if (hashMap.containsKey(house.getUnit() + house.getRoomNumber())) {
                            return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现同一个楼宇单元的房产");
                        }
                    }
                    hashMap.put(house.getUnit() + house.getRoomNumber(), 1);
                }
            }
            //存储公租房信息
            Iterable<House> saveAll = houseRepository.saveAll(houseList);

            Date date = new Date();
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            //获取用户ID
            String userId = tokenRedisVo.getId();
            //遍历循环公租房数据
            for (House save : saveAll) {
                Long orgId = save.getOrgId();
                Map<String, String> map = findEnterpriseAuditStatus(save.getProvinceId(), save.getCityId(), save.getDistrictId(), save.getStreetId(), save.getCommunityId());
                String status = map.get("status");
                //生成待办事项
                if (orgId != null && status.equals("1")) {
                    String auditOrgId = map.get("orgId");
                    BacklogItem backlogItem = new BacklogItem();
                    backlogItem.setHouseId(save.getId());
                    backlogItem.setOrgId(Long.parseLong(auditOrgId));
                    backlogItem.setUserOrgId(enterpriseId);
                    backlogItem.setType(BacklogItemTypeEnum.HOUSE_SAVE.getValue());
                    Optional<CommunityBuild> buildOptional = communityBuildRepository.findById(save.getBuildingNum());
                    Optional<CommunityBuildUnit> unitOptional = communityBuildUnitRepository.findById(save.getUnit());
                    backlogItem.setTitle(save.getResidenceCommunityName() + buildOptional.get().getName() + "栋" +
                            unitOptional.get().getName() + "单元" + save.getRoomNumber() + "号" + "房屋绑定公租房申请");
                    backlogItem.setBusinessStatus("待审批");
                    backlogItem.setHandleStatus(0);//0未读/未处理
                    backlogItem.setOperatorId(userId);
                    backlogItem.setCreateTime(date);
                    backlogItem.setAuditStatus(0);
                    backlogItemRepository.save(backlogItem);
                }
            }
            //关闭流
            work.close();
            //把数据输出给前端页面
            return ResultBean.successfulResult(saveAll);

        } catch (Exception e) {
            //打印报错日志信息
            log.error(runPositionLog.toString());
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public String pinyin(String name) {
        StringBuffer pybf = new StringBuffer();
        char[] arr = name.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (int i = 0; i < arr.length; i++) {
            if (Character.toString(arr[i]).matches("[\\u4E00-\\u9FA5]+")) {
                try {
                    String[] temp = PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat);
                    if (temp != null) {
                        pybf.append(temp[0].charAt(0));
                    }
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            } else {
                pybf.append(arr[i]);
            }
        }
        return pybf.toString().replaceAll("\\W", "").trim().substring(0, 3);
    }

    public Map<String, String> findEnterpriseAuditStatus(Long provinceId, Long cityId, Long districtId, Long streetId,
                                                         Long communityId) {
        String sql = "SELECT h.org_id as orgId,h.area_id AS areaId,h.bind_auit_status AS bindAuitStatus "
                + "FROM saas_department_hierarchy h "
                + "LEFT JOIN saas_department d ON d.id = h.org_id "
                + "LEFT JOIN saas_department_enterprise_tag_ref f ON h.id = f.dep_ref_id "
                + "WHERE f.tag_id = 5 ";

        StringBuffer buf = new StringBuffer();
        if (provinceId != null) {
            buf.append(" AND (d.province_id=").append(provinceId);
        }

        if (cityId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.city_id=").append(cityId);
            } else {
                buf.append(" or d.city_id=").append(cityId);
            }
        }

        if (districtId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.district_id=").append(districtId);
            } else {
                buf.append(" or d.district_id=").append(districtId);
            }
        }

        if (streetId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.street_id=").append(streetId);
            } else {
                buf.append(" or d.street_id=").append(streetId);
            }
        }

        if (communityId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.community_id=").append(communityId);
            } else {
                buf.append(" or d.community_id=").append(communityId);
            }
        }

        if (StringUtils.isNotEmpty(buf)) {
            buf.append(")");
        }

        Map<String, String> map = new HashMap<>();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql + buf.toString());
        if (list.isEmpty()) {
            // 结果为空，默认不需要审批
            map.put("code", "10000");
            map.put("status", "0");// 不需要审批
            map.put("orgId", null);// 审批企业ID
            return map;
        }

        // 查询公租房绑定产权单位审批人
        // 匹配社区住建局
        Long orgId = getAreaAuditStatus(list, communityId);
        // 匹配街道住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, streetId);
        }
        // 匹配区/县住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, districtId);
        }
        // 匹配市住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, cityId);
        }
        // 匹配省住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, provinceId);
        }

        if (orgId == null) {
            log.info("获取审批人信息失败, 未找到需要审批的住建局, orgId={}", orgId);
            map.put("code", "10000");
            map.put("status", "0");// 不需要审批
            map.put("orgId", null);// 审批人为节点审批人
            return map;
        }

        map.put("code", "10000");
        map.put("status", "1");// 需要审批
        map.put("orgId", String.valueOf(orgId));// 审批企业ID
        return map;
    }

    /**
     * 获取需要审批的住建局企业id
     *
     * @param list
     * @param areaId
     * @return
     * @author shun
     * @date 2021年6月30日
     */
    public Long getAreaAuditStatus(List<Map<String, Object>> list, Long areaId) {
        for (Map<String, Object> mp : list) {
            // 企业区域id
            Long id = Long.parseLong(mp.get("areaId").toString());
            if (areaId.longValue() == id.longValue() && mp.get("bindAuitStatus") != null) {
                Integer status = Integer.parseInt(mp.get("bindAuitStatus").toString());
                // 0不需要审批 1需要审批
                if (status == 1) {
                    Long orgId = Long.parseLong(mp.get("orgId").toString());
                    return orgId;
                }
            }
        }

        return null;
    }

    public ResultBean<Object> communityHouseImport(String accessToken, MultipartFile mfile, String residenceCommunityId, Long housePropertyId) {
        log.info("communityService  communityHouseImport  accessToken ={}", accessToken);
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //获取企业ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        List<String> runPositionLog = new ArrayList<>();

        try {

            //判断是否是企业用户
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            InputStream in = null;
            if (StringUtils.isBlank(residenceCommunityId)) {
                return ResultBean.failedResultWithMsg("小区为空");
            }

            if (mfile.isEmpty()) {
                return ResultBean.failedResultWithMsg("文件不存在！");
            }
            in = mfile.getInputStream();

            ImportExcelUtil importExcelUtil = new ImportExcelUtil();

            // 创建Excel工作薄
            Workbook work = importExcelUtil.getWorkbook(in, mfile.getOriginalFilename());
            if (null == work) {
                throw new Exception("创建Excel工作薄为空！");
            }

            //根据企业ID查询出对应机构
            Optional<Department> optional = departmentRepository.findById(enterpriseId);
            if (!optional.isPresent()) {
                //如果不存在
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            //获取机构数据
            Department department = optional.get();
            //获取省份ID
            Long provinceId = department.getProvinceId();//省id

            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            //物业房产
            List<House> houseList = new ArrayList<>();
            //去重验证map
            Map<String, Object> hashMap = new HashMap<>();
            Map<String, Object> waterMeterMap = new HashMap<>();
            Map<String, Object> electricMeterMap = new HashMap<>();
            Map<String, Object> gasMeterMap = new HashMap<>();

            List<CommunityBuild> buildSaveList = new ArrayList<>();
            List<CommunityBuildUnit> unitSaveList = new ArrayList<>();

            // 遍历Excel中第一个sheet  【work.getNumberOfSheets()】
                for (int i = 0; i < 1; i++) {
                sheet = work.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                Cell c = sheet.getRow(0).getCell(8);
                String str = importExcelUtil.getCellValue(c).toString();
                if (!str.equals("性质")) {
                    return ResultBean.failedResultWithMsg("请导入正确的房产信息模板");
                }

                // 遍历当前sheet中的所有行，从第5行开始读取
                for (int j = 4; j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if (row == null || row.getFirstCellNum() == j) {
                        continue;
                    }
                    //数据组装
                    House house = new House();
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        runPositionLog.add("【行列->值： " + (j + 1) + "," + (y + 1) + "-> " + cell + "  】");
                        Object cellValue = (cell == null ? cell : importExcelUtil.getCellValue(cell));

                        //楼层值
                        Object temp = importExcelUtil.getCellValue(row.getCell(2));
                        int tempValue = Integer.valueOf((String) temp);
                        //新增的楼栋的默认层数：楼层32以内按32默认，超过按他填的
                        Integer number = 0;

                        if (cellValue == null) {
                            if (y != 9 && y != 10 && y != 11) {
                                communityBuildRepository.deleteAll(buildSaveList);
                                communityBuildUnitRepository.deleteAll(unitSaveList);
                                return ResultBean.failedResultWithMsg("单元格数据有空值 | 行列：" + (j + 1) + "," + (y + 1));
                            }

                        }

                        switch (y) {
                            case 0:
                                //楼栋（栋）
                                List<CommunityBuild> list = communityBuildRepository.findByCommunityIdAndName(residenceCommunityId, (String) cellValue);
                                CommunityBuild save = new CommunityBuild();
                                if (list.size() == 0) {
                                    CommunityBuild communityBuild = new CommunityBuild();
                                    communityBuild.setCommunityId(residenceCommunityId);
                                    communityBuild.setName((String) cellValue);
                                    String value = (String) importExcelUtil.getCellValue(row.getCell(15));
                                    number = Integer.valueOf(value);
                                    communityBuild.setHouseNumber(number);
                                    switch ((String) importExcelUtil.getCellValue(row.getCell(16))) {    // 下拉选择： 居民住宅/商业/商住两用 --> 1,2,3
                                        case "居民住宅":
                                            communityBuild.setType(1);
                                            break;
                                        case "商业":
                                            communityBuild.setType(2);
                                            break;
                                        case "商住两用":
                                            communityBuild.setType(3);
                                            break;
                                    }
                                    communityBuild.setOperationId(tokenRedisVo.getId());
                                    communityBuild.setCreateTime(new Date());
                                    save = communityBuildRepository.save(communityBuild);
                                    buildSaveList.add(save);
                                } else {
                                    save = list.get(0);
                                }

                                house.setBuildingNum(save.getId());
                                break;
                            case 1:
                                //单元

                                List<CommunityBuild> buildList = communityBuildRepository.findByCommunityIdAndName(residenceCommunityId, (String) importExcelUtil.getCellValue(row.getCell(0)));
                                List<CommunityBuildUnit> unitList = communityBuildUnitRepository.findByBuildIdAndName(buildList.get(0).getId(), (String) cellValue);
                                CommunityBuildUnit unitSave = new CommunityBuildUnit();
                                if (unitList.size() == 0) {
                                    CommunityBuildUnit communityBuildUnit = new CommunityBuildUnit();
                                    communityBuildUnit.setCommunityId(residenceCommunityId);
                                    communityBuildUnit.setBuildId(buildList.get(0).getId());
                                    communityBuildUnit.setName((String) cellValue);
                                    unitSave = communityBuildUnitRepository.save(communityBuildUnit);
                                    unitSaveList.add(unitSave);
                                } else {
                                    unitSave = unitList.get(0);
                                }

                                house.setUnit(unitSave.getId());
                                break;
                            case 2:
                                //楼层
                                house.setFloor(Integer.valueOf((String) cellValue));
                                break;
                            case 3:
                                //房号
                                //同一个小区中，不能出现同一个楼宇单元的房产
                                List<CommunityBuild> builds = communityBuildRepository.findByCommunityIdAndName(residenceCommunityId, (String) importExcelUtil.getCellValue(row.getCell(0)));
                                List<CommunityBuildUnit> units = communityBuildUnitRepository.findByCommunityIdAndBuildIdAndName(residenceCommunityId, builds.get(0).getId(), (String) importExcelUtil.getCellValue(row.getCell(1)));
                                List<House> entityList = houseRepository.findByUnitAndRoomNumber(units.get(0).getId(), (String) cellValue);
                                if (entityList.size() > 0) {
                                    communityBuildRepository.deleteAll(buildSaveList);
                                    communityBuildUnitRepository.deleteAll(unitSaveList);
                                    return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现相同的房产 | 行列：" + (j + 1) + "," + (y + 1));
                                }
                                house.setRoomNumber((String) cellValue);
                                break;
                            case 4:
                                //面积（㎡）
                                if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    Double area = cell.getNumericCellValue();
                                    house.setArea(new BigDecimal(area));
                                }
                                break;
                            case 5:
                                //室
                                house.setRoom(Integer.valueOf((String) cellValue));
                                break;
                            case 6:
                                //厅
                                house.setHall(Integer.valueOf((String) cellValue));
                                break;
                            case 7:
                                //卫
                                house.setToilet(Integer.valueOf((String) cellValue));
                                break;
                            case 8:
                                //性质    [性质:1公租房、2保障房、3出租房、4自用房、5单位宿舍、6学生公寓]
                                if ((String) cellValue != null) {
                                    switch ((String) cellValue) {
                                        case "保障房":
                                            house.setHouseNature(2);
                                            break;
                                        case "出租房":
                                            house.setHouseNature(3);
                                            break;
                                        case "自用房":
                                            house.setHouseNature(4);
                                            break;
                                        case "单位宿舍":
                                            house.setHouseNature(5);
                                            break;
                                        case "学生公寓":
                                            house.setHouseNature(6);
                                            break;
                                        case "公寓":
                                            house.setHouseNature(7);
                                    }
                                } else {
                                    return ResultBean.failedResultOfParamWithMsg("请选择已有的房屋属性");
                                }
                                break;
                            case 9:
                                //水表号
                                if(cellValue != null && StringUtils.isNotBlank(String.valueOf(cellValue))){
                                    log.info("cellValue:"+cellValue+":");
                                    List<House> waterMeters = houseRepository.findByWaterMeter(cellValue.toString());
                                    if (!waterMeters.isEmpty()) {
                                        communityBuildRepository.deleteAll(buildSaveList);
                                        communityBuildUnitRepository.deleteAll(unitSaveList);
                                        return ResultBean.failedResultOfParamWithMsg("不能出现相同的水表号 | 行列：" + (j + 1) + "," + (y + 1));
                                    }
                                    house.setWaterMeter(cellValue.toString());
                                }

                                break;
                            case 10:
                                //电表号
                                if(cellValue != null && StringUtils.isNotBlank(String.valueOf(cellValue))){
                                    List<House> electricMeters = houseRepository.findByElectricMeter(cellValue.toString());
                                    if (!electricMeters.isEmpty()) {
                                        communityBuildRepository.deleteAll(buildSaveList);
                                        communityBuildUnitRepository.deleteAll(unitSaveList);
                                        return ResultBean.failedResultOfParamWithMsg("不能出现相同的电表号 | 行列：" + (j + 1) + "," + (y + 1));
                                    }
                                    house.setElectricMeter(cellValue.toString());
                                }

                                break;
                            case 11:
                                //燃气表号
                                if(cellValue != null && StringUtils.isNotBlank(String.valueOf(cellValue))){
                                List<House> gasMeters = houseRepository.findByGasMeter(cellValue.toString());
                                if (!gasMeters.isEmpty()) {
                                    communityBuildRepository.deleteAll(buildSaveList);
                                    communityBuildUnitRepository.deleteAll(unitSaveList);
                                    return ResultBean.failedResultOfParamWithMsg("不能出现相同的燃气表号 | 行列：" + (j + 1) + "," + (y + 1));
                                }
                                house.setGasMeter(cellValue.toString());
                            }

                                break;
                            case 12:
                                //产权所有人姓名
                                house.setOwnerName((String) cellValue);
                                break;
                            case 13:
                                //产权所有人电话
                                house.setOwnerMobile((String) cellValue);
                                break;
                            case 14:
                                //收费开始日期
                                house.setStartTime(cell.getDateCellValue());
                                break;
                        }
                    }

                    Optional<Community> communityOptional = communityRepository.findById(residenceCommunityId);
                    Community community = communityOptional.get();

                    Area area = areaRepository.findByIdAndLevel(community.getProvinceId(), 1);
                    String province = pinyin(area.getName());//省份3个首字母

                    // redisTemplate.opsForValue().increment("HOUSE_ID:" + province, 1);
                    // Object id = redisTemplate.opsForValue().get("HOUSE_ID:" + province);//获取redis自增id
                    //
                    // int len = 10 - id.toString().length();
                    // StringBuilder builder = new StringBuilder();
                    // for (int f = 0; f < len; f++) {//拼接成10位字符串
                    //     builder.append("0");
                    // }

                    String newId = province + System.currentTimeMillis() + RandomUtils.nextInt(1000, 10000);//省份3位首字母 + 10数字

                    house.setId(newId);  //生成id
                    house.setStatus(1);
                    house.setOperationId(tokenRedisVo.getId());
                    house.setCreateTime(new Date());
                    //产权所有人id ：根据电话号码匹配用户
                    User user = userRepository.findByMobile(house.getOwnerMobile());
                    if (user != null) {
                        house.setOwnerUserId(user.getId());
                    } else {
                        return ResultBean.failedResultOfParamWithMsg("机构不存在");
                    }

                    //判断手机号和机构名称是否一致
                    List<UserDepartmentRef> byUserId = userDepartmentRefRepository.findByUserId(user.getId());
                    if (CollectionUtil.isNotEmpty(byUserId)) {
                        Long depId = byUserId.get(0).getDepId();
                        Optional<Department> byId = departmentRepository.findById(depId);
                        if (!house.getOwnerName().equals(byId.get().getName())) {
                            return ResultBean.failedResultOfParamWithMsg("手机号与机构名称不一致");
                        }
                    }
                    //关联上产权单位机构/产出个人机构id:根据名字去匹配用户
                    //DepartmentHierarchy departmentHierarchy = departmentHierarchyRepository.findByNameAndStatus(house.getOwnerName(), 1);

                    String sql = "SELECT dh.* FROM saas_department_hierarchy dh LEFT JOIN `saas_department_enterprise_tag_ref` detr\n" +
                            "  ON dh.id = detr.dep_ref_id WHERE dh.`status` = 1 AND detr.tag_id = 2 AND dh.name = ?";
                    List<DepartmentHierarchy> departmentHierarchyList = jdbcTemplate.query(sql , new BeanPropertyRowMapper<>(DepartmentHierarchy.class), house.getOwnerName());
                    Integer ownerType = 1;
                    if (CollectionUtil.isNotEmpty(departmentHierarchyList)) {
                        DepartmentHierarchy departmentHierarchy = departmentHierarchyList.get(0);
                        Long orgId = departmentHierarchy.getOrgId();

                        //判断此机构是否在对应区域下
                        //根据企业ID查询出对应机构
                        Optional<Department> newOptional = departmentRepository.findById(orgId);
                        //获取机构数据
                        Department newDepartment = newOptional.get();
                        //获取省份ID
                        Long newProvinceId = newDepartment.getProvinceId();//省id
                        if (!provinceId.equals(newProvinceId)) {
                            return ResultBean.failedResultOfParamWithMsg("此机构不在该区域下");
                        }
                        house.setOrgId(orgId);
                        Department allByIdAndStatus = departmentRepository.findAllByIdAndStatus(orgId, 1);
                        if (allByIdAndStatus != null) {
                            ownerType = allByIdAndStatus.getOwnerType();
                        }
                    } else {
                        return ResultBean.failedResultOfParamWithMsg("该机构不是产权单位");
                    }



                    house.setResidenceCommunityId(residenceCommunityId);
                    house.setOwnerType(ownerType);   //owner_type '产权归属类型：1产权单位  2个人',

                    house.setProvinceId(community.getProvinceId());
                    house.setCityId(community.getCityId());
                    house.setDistrictId(community.getDistrictId());
                    house.setStreetId(community.getStreetId());
                    house.setCommunityId(community.getCommunityId());
                    house.setHousePropertyId(community.getOrgId()); // 所属物业
                    house.setAddress(community.getAddress());
                    house.setLeaseStatus(0);
                    house.setMatchType(1);
                    //加入集合
                    houseList.add(house);
                    if (!hashMap.isEmpty()) {  // Unit And
                        if (hashMap.containsKey(house.getUnit() + house.getRoomNumber())) {
                            communityBuildRepository.deleteAll(buildSaveList);
                            communityBuildUnitRepository.deleteAll(unitSaveList);
                            return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现相同的房产");
                        }
                    }
                    hashMap.put(house.getUnit() + house.getRoomNumber(), 1);

                    if (!waterMeterMap.isEmpty()) {
                        if (waterMeterMap.containsKey(house.getWaterMeter())) {
                            communityBuildRepository.deleteAll(buildSaveList);
                            communityBuildUnitRepository.deleteAll(unitSaveList);
                            return ResultBean.failedResultOfParamWithMsg("不能出现相同的水表号");
                        }
                    }
                    if (house.getWaterMeter() != null && StringUtils.isNotBlank(house.getWaterMeter())){
                        waterMeterMap.put(house.getWaterMeter(), 1);
                    }


                    if (!electricMeterMap.isEmpty()) {
                        if (electricMeterMap.containsKey(house.getElectricMeter())) {
                            communityBuildRepository.deleteAll(buildSaveList);
                            communityBuildUnitRepository.deleteAll(unitSaveList);
                            return ResultBean.failedResultOfParamWithMsg("不能出现相同的电表号");
                        }
                    }
                    if (house.getElectricMeter() != null && StringUtils.isNotBlank(house.getElectricMeter())){
                        electricMeterMap.put(house.getElectricMeter(), 1);
                    }


                    if (!gasMeterMap.isEmpty()) {
                        if (waterMeterMap.containsKey(house.getGasMeter())) {
                            communityBuildRepository.deleteAll(buildSaveList);
                            communityBuildUnitRepository.deleteAll(unitSaveList);
                            return ResultBean.failedResultOfParamWithMsg("不能出现相同的燃气表号");
                        }
                    }
                    if (house.getGasMeter() != null && StringUtils.isNotBlank(house.getGasMeter())){
                        gasMeterMap.put(house.getGasMeter(), 1);
                    }

                }
            }

            Iterable saveAll = houseRepository.saveAll(houseList);

            work.close();
            if (!CollectionUtil.isNotEmpty(saveAll)) {
                return ResultBean.failedResultOfParamWithMsg("请填写数据！");
            }
            return ResultBean.successfulResult(saveAll);

        } catch (Exception e) {
            log.error(runPositionLog.toString());
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    //判断空行的方法
    public boolean isRowEmpty(Row row) {

        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {

                return false;
            } else {

                break;
            }
        }
        return true;
    }

    public ResultBean<Object> communityImport(String accessToken, MultipartFile mfile) {
        log.info("communityService   communityImport  accessToken", accessToken);
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        List<String> runPositionLog = new ArrayList<>();

        try {

            InputStream in = null;
            if (mfile.isEmpty()) {
                return ResultBean.failedResultWithMsg("文件不存在！");
            }
            in = mfile.getInputStream();

            ImportExcelUtil importExcelUtil = new ImportExcelUtil();
            // 创建Excel工作薄
            Workbook work = importExcelUtil.getWorkbook(in, mfile.getOriginalFilename());
            if (null == work) {
                throw new Exception("创建Excel工作薄为空！");
            }

            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            List<CommunityVO> communityList = new ArrayList<>();
            //去重验证map
            Map<String, Object> map = new HashMap<>();
            Map<String, Object> map2 = new HashMap<>();
            List<CommunityPublicFacilities> communityPublicFacilitiesList = new ArrayList<>();
            List<CommunitySafetyFacilities> communitySafetyFacilitiesList = new ArrayList<>();
            List<CommunityServiceFacilities> communityServiceFacilitiesList = new ArrayList<>();
            List<CommunityBuildingOntology> communityBuildingOntologyList = new ArrayList<>();

            // 遍历Excel中第一个sheet  【work.getNumberOfSheets()】
            for (int i = 0; i < 1; i++) {
                sheet = work.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                Cell c = sheet.getRow(0).getCell(0);
                String str = importExcelUtil.getCellValue(c).toString();
                if (!str.equals("小区基本情况")) {
                    return ResultBean.failedResultWithMsg("请导入正确的小区信息模板");
                }

                // 遍历当前sheet中的所有行，从第6行开始读取
                for (int j = 5; j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if (row == null || row.getFirstCellNum() == j) {
                        break;
                    }
                    //数据组装
                    CommunityVO community = new CommunityVO();
                    CommunityPublicFacilitiesVO communityPublicFacilities = new CommunityPublicFacilitiesVO();
                    CommunitySafetyFacilitiesVO communitySafetyFacilities = new CommunitySafetyFacilitiesVO();
                    CommunityServiceFacilitiesVO communityServiceFacilities = new CommunityServiceFacilitiesVO();
                    CommunityBuildingOntologyVO communityBuildingOntology = new CommunityBuildingOntologyVO();

                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);


                        //验证null
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            if (y > 3) {
                                String previousOne = (row.getCell(y - 1) == null ? null : (String) importExcelUtil.getCellValue(row.getCell(y - 1))); //单元格上一个
                                String previousTwo = (row.getCell(y - 2) == null ? null : (String) importExcelUtil.getCellValue(row.getCell(y - 2))); //单元格上上个
                                if ((!"否".equals(previousOne)) && (!"否".equals(previousTwo))) {
                                    if (y != 84) {
                                        return ResultBean.failedResultWithMsg("单元格数据为空 | 行列：" + (j + 1) + "," + (y + 1));
                                    }
                                }
                            } else {
                                return ResultBean.failedResultWithMsg("单元格数据为空 | 行列：" + (j + 1) + "," + (y + 1));
                            }
                        }

                        runPositionLog.add("【行列->值： " + (j + 1) + "," + (y + 1) + "-> " + cell + "  】");
                        Object cellValue = (cell == null ? cell : importExcelUtil.getCellValue(cell));

                    /*    if(cellValue == null){
                            return ResultBean.failedResultWithMsg("单元格数据有空值 | 行列："+j+","+y);
                        }*/
                        switch (y) {
                            case 0:
                                //省
                                List<Area> provinceList = areaRepository.findByLevelAndNameLike(1, "%" + (String) cellValue + "%");
                                if (provinceList.size() > 0) {
                                    community.setProvinceId(provinceList.get(0).getId());
                                } else {
                                    return ResultBean.failedResultWithMsg("省为空或找不到");
                                }

                                break;
                            case 1:
                                //市
                                List<Area> cityList = areaRepository.findByLevelAndNameLike(2, "%" + (String) cellValue + "%");
                                if (cityList.size() > 0) {
                                    community.setCityId(cityList.get(0).getId());
                                } else {
                                    return ResultBean.failedResultWithMsg("市为空或找不到");
                                }

                                break;
                            case 2:
                                //区
                                List<Area> districtIdList = areaRepository.findByParentIdAndLevelAndNameLike(community.getCityId(), 3, "%" + (String) cellValue + "%");
                                if (districtIdList.size() > 0) {
                                    community.setDistrictId(districtIdList.get(0).getId());
                                } else {
                                    return ResultBean.failedResultWithMsg("区为空或找不到");
                                }

                                break;
                            case 3:
                                //所属街道
                                List<Area> streetIdList = areaRepository.findByParentIdAndLevelAndNameLike(community.getDistrictId(), 4, "%" + (String) cellValue + "%");
                                if (streetIdList.size() > 0) {
                                    community.setStreetId(streetIdList.get(0).getId());
                                } else {
                                    return ResultBean.failedResultWithMsg("所属街道为空或找不到");
                                }

                                break;
                            case 4:
                                //所属社区
                                List<Area> communityIdList = areaRepository.findByParentIdAndLevelAndNameLike(community.getStreetId(),5, "%" + (String) cellValue + "%");
                                if (communityIdList.size() > 0) {
                                    community.setCommunityId(communityIdList.get(0).getId());
                                } else {
                                    return ResultBean.failedResultWithMsg("所属社区为空或找不到");
                                }

                                break;
                            case 5:
                                //小区名称
                                //同一个区/县区域中，不能出现相同名称的小区
                                List<Community> entity = communityRepository.findByNameAndDistrictId((String) cellValue, community.getDistrictId());
                                if (entity.size() > 0) {
                                    return ResultBean.failedResultOfParamWithMsg("同一个区/县区域中，不能出现相同名称的小区 | 行列：" + (j + 1) + "," + (y + 1));
                                }

                                community.setName((String) cellValue);
                                break;
                            case 6:
                                //详细地址
                                List<Community> addressList = communityRepository.findByCityIdAndAddress(community.getCityId(), (String) cellValue);
                                if (addressList.size() > 0) {
                                    return ResultBean.failedResultOfParamWithMsg("同一城市，不能出现相同的小区地址 | 行列：" + (j + 1) + "," + (y + 1));
                                }
                                community.setAddress((String) cellValue);
                                break;
                            case 7:
                                //建设时间
                                //时间选择不能超过系统录入当天
                                Date date = cell.getDateCellValue();
                                if (date.getTime() > new Date().getTime()) {
                                    return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                }
                                community.setBuildTime(date);
                                break;
                            case 8:
                                //小区占地面积（万㎡）
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    community.setCommunityArea(cell.getNumericCellValue());
                                } else {
                                    community.setCommunityArea(Double.valueOf((String) cellValue));
                                }
                                break;
                            case 9:
                                //小区建筑面积（万㎡）
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    community.setCommunityTotalLandArea(cell.getNumericCellValue());
                                } else {
                                    community.setCommunityTotalLandArea(Double.valueOf((String) cellValue));
                                }
                                break;
                            case 10:
                                //小区出入口数量（个）
                                community.setInletOutletNumber(Integer.valueOf((String) cellValue));
                                break;
                            case 11:
                                //小区人口数
                                community.setPopulationNumber(Integer.valueOf((String) cellValue));
                                break;
                            case 12:
                                //违建(0无 1有)
                                community.setIllegalBuilding("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 13:
                                //违搭(0无 1有)
                                community.setBreachBuilding("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 14:
                                //架空管线(0无 1有)
                                community.setOverheadPipeline("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 15:
                                //围墙(0无 1有)
                                community.setWall("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 16:
                                //标识系统(0无 1有)
                                community.setTagSystem("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 17:
                                //绿地面积（㎡）
                                community.setGreenArea(Double.valueOf((String) cellValue));
                                break;
                            case 18:
                                //绿地率（%）
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    Double v1 = cell.getNumericCellValue();
                                    community.setGreenRate(v1);
                                } else {
                                    community.setGreenRate(Double.valueOf((String) cellValue));
                                }
                                break;
                            case 19:
                                //大门（个）
                                community.setGate(Integer.valueOf((String) cellValue));
                                break;
                            case 20:
                                //有无物业(0无 1有)
                                community.setPropertyCompany("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 21:
                                //物业收费标准
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    community.setPropertyChargingStandard(String.valueOf(cell.getNumericCellValue()));
                                } else {
                                    community.setPropertyChargingStandard((String) cellValue);

                                }
                                break;
                            case 22:
                                //是否成立业主委员会(0无 1有), 【excel传值为：是/否】
                                community.setOwnerCommittee("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 23:
                                //物业收费率（%）
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    Double v2 = cell.getNumericCellValue();
                                    community.setPropertyChargeRate(v2.toString());
                                } else {
                                    community.setPropertyChargeRate((String) cellValue);
                                }
                                break;
                            case 24:
                                //小区物业公司名称
                                DepartmentHierarchy departmentHierarchy = departmentHierarchyRepository.findByNameAndStatus((String) cellValue, 1);
                                if (departmentHierarchy == null) {
                                    return ResultBean.failedResultWithMsg("小区物业公司[" + cellValue + "]未找到，请检查excel | 行列：" + (j + 1) + "," + (y + 1));
                                }
                                community.setOrgId(departmentHierarchy.getOrgId());//物业企业id
                                community.setPropertyCompanyName((String) cellValue);
                                break;
                            case 25:
                                //物业公司管理范围
                                community.setPropertyCompanyLimit((String) cellValue);
                                break;
                            case 26:
                                //行车道宽度（米）
                                communityPublicFacilities.setLaneWidth(new BigDecimal((String) cellValue));
                                break;
                            case 27:
                                //机动车位（个）
                                communityPublicFacilities.setMotorVehicles(Integer.valueOf((String) cellValue));
                                break;
                            case 28:
                                //非机动车位（个）
                                communityPublicFacilities.setNonMotorVehicles(Integer.valueOf((String) cellValue));
                                break;
                            case 29:
                                //专用人行道(0无 1有)
                                communityPublicFacilities.setPrivateWalkway("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 30:
                                //自行车道(0无 1有)
                                communityPublicFacilities.setBikeway("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 31:
                                //交通标示
                                communityPublicFacilities.setTrafficSigns("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 32:
                                //健身步道
                                communityPublicFacilities.setFitnessTrails("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 33:
                                //主要道路材质
                                communityPublicFacilities.setMainRoadMaterial((String) cellValue);
                                break;
                            case 34:
                                //供水主管线改造(0否 1是)
                                communityPublicFacilities.setMainWaterSupply("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 35:
                                //供水主管线改造时间
                                Date date1 = cell.getDateCellValue();
                                if (date1 != null) {
                                    if (date1.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setModificationTime(cell.getDateCellValue());
                                }
                                break;
                            case 36:
                                //供水支管线改造(0否 1是)
                                communityPublicFacilities.setPipeWaterSupply("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 37:
                                //供水支管线改造时间
                                Date date2 = cell.getDateCellValue();
                                if (date2 != null) {
                                    if (date2.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setPipeWaterSupplyTime(cell.getDateCellValue());
                                }
                                break;
                            case 38:
                                //供电主线改造(0否 1是)
                                communityPublicFacilities.setPowerSupplyLine("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 39:
                                //供电主线改造时间
                                Date date3 = cell.getDateCellValue();
                                if (date3 != null) {
                                    if (date3.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setPowerSupplyLineTime(cell.getDateCellValue());
                                }
                                break;
                            case 40:
                                //供电支线改造(0否 1是)
                                communityPublicFacilities.setPowerSupplyBranch("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 41:
                                //供电支线改造时间
                                Date date4 = cell.getDateCellValue();
                                if (date4 != null) {
                                    if (date4.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setPowerSupplyBranchTime(cell.getDateCellValue());
                                }
                                break;
                            case 42:
                                //供热主线改造(0否 1是)
                                communityPublicFacilities.setHeatingMainLine("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 43:
                                //供热主线改造时间
                                Date date5 = cell.getDateCellValue();
                                if (date5 != null) {
                                    if (date5.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setHeatingMainLineTime(cell.getDateCellValue());
                                }
                                break;
                            case 44:
                                //供热支线改造(0否 1是)
                                communityPublicFacilities.setHeatingFeeder("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 45:
                                //供热支线改造时间
                                Date date6 = cell.getDateCellValue();
                                if (date6 != null) {
                                    if (date6.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setHeatingFeederTime(cell.getDateCellValue());
                                }
                                break;
                            case 46:
                                //燃气主线改造(0否 1是)
                                communityPublicFacilities.setGasLines("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 47:
                                //燃气主线改造时间
                                Date date7 = cell.getDateCellValue();
                                if (date7 != null) {
                                    if (date7.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setGasLinesTime(cell.getDateCellValue());
                                }
                                break;
                            case 48:
                                //燃气支线改造(0否 1是)
                                communityPublicFacilities.setGasFeeder("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 49:
                                //燃气支线改造时间
                                Date date8 = cell.getDateCellValue();
                                if (date8 != null) {
                                    if (date8.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setGasFeederTime(cell.getDateCellValue());
                                }
                                break;
                            case 50:
                                //排水主线改造(0否 1是)
                                communityPublicFacilities.setMainDrainage("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 51:
                                //排水主线时间
                                Date date9 = cell.getDateCellValue();
                                if (date9 != null) {
                                    if (date9.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setMainDrainageTime(cell.getDateCellValue());
                                }
                                break;
                            case 52:
                                //排水支线改造(0否 1是)
                                communityPublicFacilities.setBranchDrainage("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 53:
                                //排水支线改造时间
                                Date date10 = cell.getDateCellValue();
                                if (date10 != null) {
                                    if (date10.getTime() > new Date().getTime()) {
                                        return ResultBean.failedResultWithMsg("时间选择不能超过系统录入当天");
                                    }
                                    communityPublicFacilities.setBranchDrainageTime(cell.getDateCellValue());
                                }
                                break;
                            case 54:
                                //路灯（盏）
                                communityPublicFacilities.setStreet(Integer.valueOf((String) cellValue));
                                break;
                            case 55:
                                //公告景观照明(0无 1有)
                                communityPublicFacilities.setAnnouncementLandscapeLighting("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 56:
                                //垃圾收集设施（个）
                                communityPublicFacilities.setRefuseCollectionFacility(Integer.valueOf((String) cellValue));
                                break;
                            case 57:
                                //垃圾分类（个）
                                communityPublicFacilities.setGarbageSorting(Integer.valueOf((String) cellValue));
                                break;
                            case 58:
                                //废品收购点(0无 1有)
                                communityPublicFacilities.setScrapPoint("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 59:
                                //公共厕所（个）
                                communityPublicFacilities.setPublicToilet(Integer.valueOf((String) cellValue));
                                break;
                            case 60:
                                //通信基站(0无 1有)
                                communityPublicFacilities.setCommunicationStation("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 61:
                                //通信管道(0无 1有)
                                communityPublicFacilities.setCommunicationChannel("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 62:
                                //通信设施用房(0无 1有)
                                communityPublicFacilities.setCommunicationFacilities("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 63:
                                //光纤入户(0无 1有)
                                communityPublicFacilities.setOpticalFiber("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 64:
                                //消防通道(0无 1有)
                                communitySafetyFacilities.setFireEngineAccess("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 65:
                                //消防设施(0无 1有)
                                communitySafetyFacilities.setFireFightingDevice("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 66:
                                //门禁系统(0无 1有)
                                communitySafetyFacilities.setDoorSystem("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 67:
                                //安防监控系统（个）
                                communitySafetyFacilities.setSucurityMonitorSystem(Integer.valueOf((String) cellValue));
                                break;
                            case 68:
                                //防雷设施(0无 1有)
                                communitySafetyFacilities.setThunderFacilities("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 69:
                                //有无应急避难场地(0无 1有)
                                communitySafetyFacilities.setEmergencyShelter("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 70:
                                //有无应急避难建筑(0无 1有)
                                communitySafetyFacilities.setEmergencyShelterBuilding("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 71:
                                //室外活动广场(0无 1有)
                                communityServiceFacilities.setOutdoorPlaza("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 72:
                                //室外健身设施(0无 1有)
                                communityServiceFacilities.setOutdoorFitnessFacilities("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 73:
                                //儿童活动设施(0无 1有)
                                communityServiceFacilities.setChildActivityFacilities("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 74:
                                //室外休息设施(0无 1有)
                                communityServiceFacilities.setOutdoorRestingFacilities("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 75:
                                //宣传栏(0无 1有)
                                communityServiceFacilities.setBulletinBoard("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 76:
                                //建筑出入口无障碍坡道(0无 1有)
                                communityServiceFacilities.setEntranceWheelchairRamp("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 77:
                                //户外场地无障碍坡道(0无 1有)
                                communityServiceFacilities.setOutdoorWheelchairRamp("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 78:
                                //社区服务用房(0无 1有)
                                communityServiceFacilities.setCommunityServices("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 79:
                                //信报箱(0无 1有)
                                communityServiceFacilities.setNewCabinet("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 80:
                                //物业管理用房(0无 1有)
                                communityServiceFacilities.setPropertyManagementOccupancy("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 81:
                                //快递收发设施(0无 1有)
                                communityServiceFacilities.setExpressDeliveryFacilities("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 82:
                                //老年人服务用房(0无 1有)
                                communityServiceFacilities.setElderlyService("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 83:
                                //充电桩(0无 1有)
                                communityServiceFacilities.setChargingPile("无".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 84:
                                //充电桩数
                                String tem = (String) cellValue;
                                String previousOne = (String) importExcelUtil.getCellValue(row.getCell(y - 1)); //单元格上一个
                                if (StringUtils.isNotBlank(tem)) {
                                    communityServiceFacilities.setChargingPileNumber(Integer.valueOf(tem));
                                } else {
                                    if (!"无".equals(previousOne)) {
                                        return ResultBean.failedResultWithMsg("单元格数据为空 | 行列：" + (j + 1) + "," + (y + 1));
                                    }
                                }
                                break;
                            case 85:
                                //外墙体温改造(0否 1是)
                                communityBuildingOntology.setWallTemperatureRemould("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 86:
                                //外墙体温改造时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setModificationTime(cell.getDateCellValue());
                                }
                                break;
                            case 87:
                                //外墙体温改造栋数
                                String temp = (String) cellValue;
                                if (StringUtils.isNotBlank(temp)) {
                                    communityBuildingOntology.setRenovationBuildingNumber(Integer.valueOf(temp));
                                }
                                break;
                            case 88:
                                //外立面改造(0否 1是)
                                communityBuildingOntology.setReconstructionFacade("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 89:
                                //外立面改造时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setReconstructionFacadeTime(cell.getDateCellValue());
                                }
                                break;
                            case 90:
                                //外立面改造栋数
                                String temp1 = (String) cellValue;
                                if (StringUtils.isNotBlank(temp1)) {
                                    communityBuildingOntology.setReconstructionFacadeNumber(Integer.valueOf(temp1));
                                }
                                break;
                            case 91:
                                //楼道管线改造(0否 1是)
                                communityBuildingOntology.setCorridorPipelineRenovation("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 92:
                                //楼道管线改造时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setPipelineRenovationTime(cell.getDateCellValue());
                                }
                                break;
                            case 93:
                                //楼道管线改造栋数
                                String temp2 = (String) cellValue;
                                if (StringUtils.isNotBlank(temp2)) {
                                    communityBuildingOntology.setPipelineRenovationNumber(Integer.valueOf(temp2));
                                }
                                break;
                            case 94:
                                //水电气出户改造(0否 1是)
                                communityBuildingOntology.setOutdoorWaterElectricity("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 95:
                                //水电气出户改造时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setWaterElectricityTime(cell.getDateCellValue());
                                }
                                break;
                            case 96:
                                //水电气出户改造栋数
                                String temp3 = (String) cellValue;
                                if (StringUtils.isNotBlank(temp3)) {
                                    communityBuildingOntology.setWaterElectricityNumber(Integer.valueOf(temp3));
                                }
                                break;
                            case 97:
                                //楼道照明改造(0否 1是)
                                communityBuildingOntology.setCorridorLightingRenovation("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 98:
                                //楼道照明时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setLightingRenovationTime(cell.getDateCellValue());
                                }
                                break;
                            case 99:
                                //楼道照明栋数
                                String temp4 = (String) cellValue;
                                if (StringUtils.isNotBlank(temp4)) {
                                    communityBuildingOntology.setLightingRenovationNumber(Integer.valueOf(temp4));
                                }
                                break;
                            case 100:
                                //楼体散水改造(0否 1是)
                                communityBuildingOntology.setBuildingScatteredWater("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 101:
                                //楼体散水改造时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setScatteredWaterTime(cell.getDateCellValue());
                                }
                                break;
                            case 102:
                                //楼体散水改造栋数
                                String temp5 = (String) cellValue;
                                if (StringUtils.isNotBlank(temp5)) {
                                    communityBuildingOntology.setScatteredWaterNumber(Integer.valueOf(temp5));
                                }
                                break;
                            case 103:
                                //楼道门窗改造(0否 1是)
                                communityBuildingOntology.setConrridorDoorWindows("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 104:
                                //楼道门窗改造时间
                                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                    communityBuildingOntology.setDoorWindowsTime(cell.getDateCellValue());
                                }
                                break;
                            case 105:
                                //楼道门窗改造栋数
                                String temp6 = (String) cellValue;
                                if (StringUtils.isNotBlank(temp6)) {
                                    communityBuildingOntology.setDoorWindowsNumber(Integer.valueOf(temp6));
                                }
                                break;
                            case 106:
                                //有无加装电梯(0否 1是)
                                communityBuildingOntology.setWhetherElevatorInstalled("否".equals(((String) cellValue)) ? 0 : 1);
                                break;
                            case 107:
                                //楼梯抗震加固(0否 1是)
                                communityBuildingOntology.setSeismicReinforcementStairs("否".equals(((String) cellValue)) ? 0 : 1);
                                break;

                            case 108:
                                if (ObjectUtil.isNotNull(cellValue)) {
                                    community.setLongitude(Double.valueOf((String) cellValue));
                                } else {
                                    return ResultBean.failedResultWithMsg("经度不能为空");
                                }
                                break;

                            case 109:
                                if (ObjectUtil.isNotNull(cellValue)) {
                                    community.setLatitude(Double.valueOf((String) cellValue));
                                } else {
                                    return ResultBean.failedResultWithMsg("纬度不能为空");
                                }
                                break;

                        }

                    }


                    community.setStatus(1); //状态：1正常  4已删除
                    community.setOperationId(tokenRedisVo.getId());
                    community.setCreateTime(new Date());
                    community.setId("X" + KeyWorker.nextId());  //生成id
                    //加入集合
                    community.setCommunityPublicFacilitiesVO(communityPublicFacilities);
                    community.setCommunitySafetyFacilitiesVO(communitySafetyFacilities);
                    community.setCommunityServiceFacilitiesVO(communityServiceFacilities);
                    community.setCommunityBuildingOntologyVO(communityBuildingOntology);
                    communityList.add(community);

                    if (!map.isEmpty()) {
                        if (map.containsKey(community.getDistrictId() + community.getName())) {
                            return ResultBean.failedResultOfParamWithMsg("同一个区/县区域中，不能出现相同名称的小区");
                        }
                    }
                    map.put(community.getDistrictId() + community.getName(), 1);

                    if (!map2.isEmpty()) {
                        if (map2.containsKey(community.getCityId() + community.getAddress())) {
                            return ResultBean.failedResultOfParamWithMsg("同一城市，不能出现相同的小区地址");
                        }
                    }
                    map2.put(community.getCityId() + community.getAddress(), 1);
                }
            }


            for (CommunityVO vo : communityList) {

                Community community = new Community();
                BeanUtils.copyProperties(vo, community);
                Community save = communityRepository.save(community);

                CommunityPublicFacilities communityPublicFacilities = new CommunityPublicFacilities();
                BeanUtils.copyProperties(vo.getCommunityPublicFacilitiesVO(), communityPublicFacilities);
                communityPublicFacilities.setCommunityId(save.getId());
                communityPublicFacilitiesRepository.save(communityPublicFacilities);
                CommunitySafetyFacilities communitySafetyFacilities = new CommunitySafetyFacilities();
                BeanUtils.copyProperties(vo.getCommunitySafetyFacilitiesVO(), communitySafetyFacilities);
                communitySafetyFacilities.setCommunityId(save.getId());
                communitySafetyFacilitiesRepository.save(communitySafetyFacilities);
                CommunityServiceFacilities communityServiceFacilities = new CommunityServiceFacilities();
                BeanUtils.copyProperties(vo.getCommunityServiceFacilitiesVO(), communityServiceFacilities);
                communityServiceFacilities.setCommunityId(save.getId());
                communityServiceFacilitiesRepository.save(communityServiceFacilities);
                CommunityBuildingOntology communityBuildingOntology = new CommunityBuildingOntology();
                BeanUtils.copyProperties(vo.getCommunityBuildingOntologyVO(), communityBuildingOntology);
                communityBuildingOntology.setCommunityId(save.getId());
                communityBuildingOntologyRepository.save(communityBuildingOntology);
            }

            work.close();
            return ResultBean.successfulResult(communityList);

        } catch (Exception e) {
            log.error(runPositionLog.toString());
            log.error(e.getMessage(), e);
            return ResultBean.failedResultWithMsg("导入excel格式或数据有误！");
        }

    }

    public void communityExport(String accessToken, HttpServletResponse response, CommunityVO vo, List<String> idList) throws Exception {
        log.info("communityService  communityExport  accessToken={} ", accessToken);

        // 获取总数
        Integer pageSize = 10000;
        List<CommunityVO> data = new ArrayList<>();
        // 表格头
        Map<String, String> map = new LinkedHashMap<>();
        // 文件名
        String fileName = "";

        // 分批查询数据, 每次查询1000条
        for (int pageNumber = 0; ; pageNumber++) {
            ApiData<CommunityVO> apiData = appCommunityService.findCommunityList(accessToken, pageNumber, pageSize, vo, idList).getResultData();

            List<CommunityVO> pageData = apiData.getData();
            if (pageData.isEmpty() || pageNumber >= 200) {
                break;
            }
            data.addAll(pageData);
        }

        List<CommunityExportParameterVO> resultData = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy年MM月dd日");

        for (CommunityVO c : data) {

            Integer houseNum = jdbcTemplate.queryForObject(" select COUNT(1) from saas_house_eligible_apply a left join saas_house_match_user u on a.id=u.apply_id left join saas_house h on h.id=u.house_id where a.apply_status=3  "
                    + " and h.residence_community_id ='" + c.getId() + "'", Integer.class);

            //楼房总栋数
            List<CommunityBuild> buildTotal = communityBuildRepository.findByCommunityId(c.getId());
            //层数
            int houseNumberTotal = 0;
            for (CommunityBuild b : buildTotal) {
                Integer houseNumber = b.getHouseNumber();
                houseNumberTotal += houseNumber;
            }

            CommunityExportParameterVO cep = new CommunityExportParameterVO();
            cep.setProvinceName(c.getProvinceName());
            cep.setCityName(c.getCityName());
            cep.setDistrictName(c.getDistrictName());
            cep.setStreetName(c.getStreetName());
            cep.setCommunityName(c.getCommunityName());
            cep.setName(c.getName());
            cep.setAddress(c.getAddress());
            cep.setBuildTime(sdf.format(c.getBuildTime()));
            cep.setCommunityArea(c.getCommunityArea());
            cep.setCommunityTotalLandArea(c.getCommunityTotalLandArea());
            cep.setInletOutletNumber(c.getInletOutletNumber());
            cep.setBuildNum(c.getBuildNum());
            cep.setUnitNum(c.getUnitNum());
            cep.setFloorNum(houseNumberTotal);
            cep.setHouseNum(houseNum);
            cep.setPopulationNumber(c.getPopulationNumber());
            cep.setIllegalBuilding(c.getIllegalBuilding() == 0 ? "无" : "有"); //(0无 1有)
            cep.setBreachBuilding(c.getBreachBuilding() == 0 ? "无" : "有");
            cep.setOverheadPipeline(c.getOverheadPipeline() == 0 ? "无" : "有");
            cep.setWall(c.getWall() == 0 ? "无" : "有");
            cep.setTagSystem(c.getTagSystem() == 0 ? "无" : "有");
            cep.setGreenArea(c.getGreenArea());
            cep.setGreenRate(c.getGreenRate() + "%");
            cep.setGate(c.getGate());
            if (c.getPropertyCompany() != null) {
                cep.setPropertyCompany(c.getPropertyCompany() == 0 ? "无" : "有");
            }
            cep.setPropertyChargingStandard(c.getPropertyChargingStandard() + "元/平方米/月");
            if (c.getOwnerCommittee() != null) {
                cep.setOwnerCommittee(c.getOwnerCommittee() == 0 ? "否" : "是");//0否 1是
            }
            cep.setPropertyChargeRate(c.getPropertyChargeRate() + "%");
            cep.setPropertyCompanyName(c.getPropertyCompanyName());
            cep.setPropertyCompanyLimit(c.getPropertyCompanyLimit());
            cep.setLaneWidth(c.getCommunityPublicFacilitiesVO().getLaneWidth());
            cep.setMotorVehicles(c.getCommunityPublicFacilitiesVO().getMotorVehicles());
            cep.setNonMotorVehicles(c.getCommunityPublicFacilitiesVO().getNonMotorVehicles());
            if (c.getCommunityPublicFacilitiesVO().getPrivateWalkway() != null) {
                cep.setPrivateWalkway(c.getCommunityPublicFacilitiesVO().getPrivateWalkway() == 0 ? "无" : "有"); //(0无 1有)
            }
            if (c.getCommunityPublicFacilitiesVO().getBikeway() != null) {
                cep.setBikeway(c.getCommunityPublicFacilitiesVO().getBikeway() == 0 ? "无" : "有");
            }
            if (c.getCommunityPublicFacilitiesVO().getTrafficSigns() != null) {
                cep.setTrafficSigns(c.getCommunityPublicFacilitiesVO().getTrafficSigns() == 0 ? "无" : "有");
            }
            if (c.getCommunityPublicFacilitiesVO().getFitnessTrails() != null) {
                cep.setFitnessTrails(c.getCommunityPublicFacilitiesVO().getFitnessTrails() == 0 ? "无" : "有");
            }
            cep.setMainRoadMaterial(c.getCommunityPublicFacilitiesVO().getMainRoadMaterial());
            if (c.getCommunityPublicFacilitiesVO().getMainWaterSupply() != null) {
                cep.setMainWaterSupply(c.getCommunityPublicFacilitiesVO().getMainWaterSupply() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getModificationTime())); //(0否 1是)
            }
            if (c.getCommunityPublicFacilitiesVO().getPipeWaterSupply() != null) {
                cep.setPipeWaterSupply(c.getCommunityPublicFacilitiesVO().getPipeWaterSupply() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getPipeWaterSupplyTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getPowerSupplyLine() != null) {
                cep.setPowerSupplyLine(c.getCommunityPublicFacilitiesVO().getPowerSupplyLine() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getPowerSupplyLineTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getPowerSupplyBranch() != null) {
                cep.setPowerSupplyBranch(c.getCommunityPublicFacilitiesVO().getPowerSupplyBranch() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getPowerSupplyBranchTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getHeatingMainLine() != null) {
                cep.setHeatingMainLine(c.getCommunityPublicFacilitiesVO().getHeatingMainLine() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getHeatingMainLineTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getHeatingFeeder() != null) {
                cep.setHeatingFeeder(c.getCommunityPublicFacilitiesVO().getHeatingFeeder() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getHeatingFeederTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getGasLines() != null) {
                cep.setGasLines(c.getCommunityPublicFacilitiesVO().getGasLines() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getGasLinesTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getGasFeeder() != null) {
                cep.setGasFeeder(c.getCommunityPublicFacilitiesVO().getGasFeeder() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getGasFeederTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getMainDrainage() != null) {
                cep.setMainDrainage(c.getCommunityPublicFacilitiesVO().getMainDrainage() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getMainDrainageTime()));
            }
            if (c.getCommunityPublicFacilitiesVO().getBranchDrainage() != null) {
                cep.setBranchDrainage(c.getCommunityPublicFacilitiesVO().getBranchDrainage() == 0 ? "否" : sdf.format(c.getCommunityPublicFacilitiesVO().getBranchDrainageTime()));
            }
            cep.setStreet(c.getCommunityPublicFacilitiesVO().getStreet());
            if (c.getCommunityPublicFacilitiesVO().getAnnouncementLandscapeLighting() != null) {
                cep.setAnnouncementLandscapeLighting(c.getCommunityPublicFacilitiesVO().getAnnouncementLandscapeLighting() == 0 ? "无" : "有");
            }
            cep.setRefuseCollectionFacility(c.getCommunityPublicFacilitiesVO().getRefuseCollectionFacility());
            cep.setGarbageSorting(c.getCommunityPublicFacilitiesVO().getGarbageSorting());
            if (c.getCommunityPublicFacilitiesVO().getScrapPoint() != null) {
                cep.setScrapPoint(c.getCommunityPublicFacilitiesVO().getScrapPoint() == 0 ? "无" : "有");
            }
            cep.setPublicToilet(c.getCommunityPublicFacilitiesVO().getPublicToilet());
            if (c.getCommunityPublicFacilitiesVO().getCommunicationStation() != null) {
                cep.setCommunicationStation(c.getCommunityPublicFacilitiesVO().getCommunicationStation() == 0 ? "无" : "有");
            }
            if (c.getCommunityPublicFacilitiesVO().getCommunicationChannel() != null) {
                cep.setCommunicationChannel(c.getCommunityPublicFacilitiesVO().getCommunicationChannel() == 0 ? "无" : "有");
            }
            if (c.getCommunityPublicFacilitiesVO().getCommunicationFacilities() != null) {
                cep.setCommunicationFacilities(c.getCommunityPublicFacilitiesVO().getCommunicationFacilities() == 0 ? "无" : "有");
            }
            if (c.getCommunityPublicFacilitiesVO().getOpticalFiber() != null) {
                cep.setOpticalFiber(c.getCommunityPublicFacilitiesVO().getOpticalFiber() == 0 ? "无" : "有");
            }
            if (c.getCommunitySafetyFacilitiesVO().getFireEngineAccess() != null) {
                cep.setFireEngineAccess(c.getCommunitySafetyFacilitiesVO().getFireEngineAccess() == 0 ? "无" : "有");
            }
            if (c.getCommunitySafetyFacilitiesVO().getFireFightingDevice() != null) {
                cep.setFireFightingDevice(c.getCommunitySafetyFacilitiesVO().getFireFightingDevice() == 0 ? "无" : "有");
            }
            if (c.getCommunitySafetyFacilitiesVO().getDoorSystem() != null) {
                cep.setDoorSystem(c.getCommunitySafetyFacilitiesVO().getDoorSystem() == 0 ? "无" : "有");
            }
            cep.setSucurityMonitorSystem(c.getCommunitySafetyFacilitiesVO().getSucurityMonitorSystem());
            if (c.getCommunitySafetyFacilitiesVO().getThunderFacilities() != null) {
                cep.setThunderFacilities(c.getCommunitySafetyFacilitiesVO().getThunderFacilities() == 0 ? "无" : "有");
            }
            if (c.getCommunitySafetyFacilitiesVO().getEmergencyShelter() != null) {
                cep.setEmergencyShelter(c.getCommunitySafetyFacilitiesVO().getEmergencyShelter() == 0 ? "无" : "有");
            }
            if (c.getCommunitySafetyFacilitiesVO().getEmergencyShelterBuilding() != null) {
                cep.setEmergencyShelterBuilding(c.getCommunitySafetyFacilitiesVO().getEmergencyShelterBuilding() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getOutdoorPlaza() != null) {
                cep.setOutdoorPlaza(c.getCommunityServiceFacilitiesVO().getOutdoorPlaza() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getOutdoorFitnessFacilities() != null) {
                cep.setOutdoorFitnessFacilities(c.getCommunityServiceFacilitiesVO().getOutdoorFitnessFacilities() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getChildActivityFacilities() != null) {
                cep.setChildActivityFacilities(c.getCommunityServiceFacilitiesVO().getChildActivityFacilities() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getOutdoorRestingFacilities() != null) {
                cep.setOutdoorRestingFacilities(c.getCommunityServiceFacilitiesVO().getOutdoorRestingFacilities() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getBulletinBoard() != null) {
                cep.setBulletinBoard(c.getCommunityServiceFacilitiesVO().getBulletinBoard() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getEntranceWheelchairRamp() != null) {
                cep.setEntranceWheelchairRamp(c.getCommunityServiceFacilitiesVO().getEntranceWheelchairRamp() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getOutdoorWheelchairRamp() != null) {
                cep.setOutdoorWheelchairRamp(c.getCommunityServiceFacilitiesVO().getOutdoorWheelchairRamp() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getCommunityServices() != null) {
                cep.setCommunityServices(c.getCommunityServiceFacilitiesVO().getCommunityServices() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getNewCabinet() != null) {
                cep.setNewCabinet(c.getCommunityServiceFacilitiesVO().getNewCabinet() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getPropertyManagementOccupancy() != null) {
                cep.setPropertyManagementOccupancy(c.getCommunityServiceFacilitiesVO().getPropertyManagementOccupancy() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getExpressDeliveryFacilities() != null) {
                cep.setExpressDeliveryFacilities(c.getCommunityServiceFacilitiesVO().getExpressDeliveryFacilities() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getElderlyService() != null) {
                cep.setElderlyService(c.getCommunityServiceFacilitiesVO().getElderlyService() == 0 ? "无" : "有");
            }
            if (c.getCommunityServiceFacilitiesVO().getChargingPile() != null) {
                cep.setChargingPile(c.getCommunityServiceFacilitiesVO().getChargingPile() == 0 ? "无" : c.getCommunityServiceFacilitiesVO().getChargingPileNumber().toString());
            }
            if (c.getCommunityBuildingOntologyVO().getWallTemperatureRemould() != null) {
                cep.setWallTemperatureRemould(c.getCommunityBuildingOntologyVO().getWallTemperatureRemould() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getModificationTime()) + " " + c.getCommunityBuildingOntologyVO().getRenovationBuildingNumber() + "栋"); //(0否 1是)
                if (c.getCommunityBuildingOntologyVO().getWallTemperatureRemould() == 1) {
                    cep.setWallModificationTime(sdf2.format(c.getCommunityBuildingOntologyVO().getModificationTime()));
                    cep.setRenovationBuildingNumber(c.getCommunityBuildingOntologyVO().getRenovationBuildingNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getReconstructionFacade() != null) {
                cep.setReconstructionFacade(c.getCommunityBuildingOntologyVO().getReconstructionFacade() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getReconstructionFacadeTime()) + " " + c.getCommunityBuildingOntologyVO().getReconstructionFacadeNumber() + "栋");
                if (c.getCommunityBuildingOntologyVO().getReconstructionFacade() == 1) {
                    cep.setReconstructionFacadeTime(sdf2.format(c.getCommunityBuildingOntologyVO().getReconstructionFacadeTime()));
                    cep.setReconstructionFacadeNumber(c.getCommunityBuildingOntologyVO().getReconstructionFacadeNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getCorridorPipelineRenovation() != null) {
                cep.setCorridorPipelineRenovation(c.getCommunityBuildingOntologyVO().getCorridorPipelineRenovation() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getPipelineRenovationTime()) + " " + c.getCommunityBuildingOntologyVO().getPipelineRenovationNumber() + "栋");
                if (c.getCommunityBuildingOntologyVO().getCorridorPipelineRenovation() == 1) {
                    cep.setPipelineRenovationTime(sdf2.format(c.getCommunityBuildingOntologyVO().getPipelineRenovationTime()));
                    cep.setPipelineRenovationNumber(c.getCommunityBuildingOntologyVO().getPipelineRenovationNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getOutdoorWaterElectricity() != null) {
                cep.setOutdoorWaterElectricity(c.getCommunityBuildingOntologyVO().getOutdoorWaterElectricity() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getWaterElectricityTime()) + " " + c.getCommunityBuildingOntologyVO().getWaterElectricityNumber() + "栋");
                if (c.getCommunityBuildingOntologyVO().getOutdoorWaterElectricity() == 1) {
                    cep.setWaterElectricityTime(sdf2.format(c.getCommunityBuildingOntologyVO().getWaterElectricityTime()));
                    cep.setWaterElectricityNumber(c.getCommunityBuildingOntologyVO().getWaterElectricityNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getCorridorLightingRenovation() != null) {
                cep.setCorridorLightingRenovation(c.getCommunityBuildingOntologyVO().getCorridorLightingRenovation() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getLightingRenovationTime()) + " " + c.getCommunityBuildingOntologyVO().getLightingRenovationNumber() + "栋");
                if (c.getCommunityBuildingOntologyVO().getCorridorLightingRenovation() == 1) {
                    cep.setLightingRenovationTime(sdf2.format(c.getCommunityBuildingOntologyVO().getLightingRenovationTime()));
                    cep.setLightingRenovationNumber(c.getCommunityBuildingOntologyVO().getLightingRenovationNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getBuildingScatteredWater() != null) {
                cep.setBuildingScatteredWater(c.getCommunityBuildingOntologyVO().getBuildingScatteredWater() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getScatteredWaterTime()) + " " + c.getCommunityBuildingOntologyVO().getScatteredWaterNumber() + "栋");
                if (c.getCommunityBuildingOntologyVO().getBuildingScatteredWater() == 1) {
                    cep.setScatteredWaterTime(sdf2.format(c.getCommunityBuildingOntologyVO().getScatteredWaterTime()));
                    cep.setScatteredWaterNumber(c.getCommunityBuildingOntologyVO().getScatteredWaterNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getConrridorDoorWindows() != null) {
                cep.setConrridorDoorWindows(c.getCommunityBuildingOntologyVO().getConrridorDoorWindows() == 0 ? "否" : sdf2.format(c.getCommunityBuildingOntologyVO().getDoorWindowsTime()) + " " + c.getCommunityBuildingOntologyVO().getDoorWindowsNumber() + "栋");
                if (c.getCommunityBuildingOntologyVO().getConrridorDoorWindows() == 1) {
                    cep.setDoorWindowsTime(sdf2.format(c.getCommunityBuildingOntologyVO().getDoorWindowsTime()));
                    cep.setDoorWindowsNumber(c.getCommunityBuildingOntologyVO().getDoorWindowsNumber() + "栋");
                }
            }
            if (c.getCommunityBuildingOntologyVO().getWhetherElevatorInstalled() != null) {
                cep.setWhetherElevatorInstalled(c.getCommunityBuildingOntologyVO().getWhetherElevatorInstalled() == 0 ? "否" : "是");
            }
            if (c.getCommunityBuildingOntologyVO().getSeismicReinforcementStairs() != null) {
                cep.setSeismicReinforcementStairs(c.getCommunityBuildingOntologyVO().getSeismicReinforcementStairs() == 0 ? "否" : "是");
            }

            resultData.add(cep);
        }

        map.put("省名称", "provinceName");
        map.put("市名称", "cityName");
        map.put("区名称", "districtName");
        map.put("所属街道名", "streetName");
        map.put("所属社区名", "communityName");
        map.put(" 小区名称", "name");
        map.put("小区地址", "address");
        map.put("建设时间", "buildTime");
        map.put("小区占地面积", "communityArea");
        map.put("小区总用地面积", "communityTotalLandArea");
        map.put("小区出入口数量", "inletOutletNumber");
        map.put("小区楼宇数", "buildNum");
        map.put("小区单元数", "unitNum");
        map.put("小区层数", "floorNum");
        map.put("小区户数", "houseNum");
        map.put("小区人口数", "populationNumber");
        map.put("违建", "illegalBuilding");
        map.put("违搭", "breachBuilding");
        map.put("架空管线", "overheadPipeline");
        map.put("围墙", "wall");
        map.put("标识系统", "tagSystem");
        map.put("绿地面积", "greenArea");
        map.put("绿地率", "greenRate");
        map.put("大门", "gate");
        map.put("有无物业", "propertyCompany");
        map.put("物业收费标准", "propertyChargingStandard");
        map.put("是否成立业主委员会", "ownerCommittee");
        map.put("物业收费率", "propertyChargeRate");
        map.put("小区物业公司名称", "propertyCompanyName");
        map.put("物业公司管理范围", "propertyCompanyLimit");
        map.put("行车道宽度", "laneWidth");
        map.put("机动车位", "motorVehicles");
        map.put("非机动车位", "nonMotorVehicles");
        map.put("专用人行道", "privateWalkway");
        map.put("自行车道", "bikeway");
        map.put("交通标示", "trafficSigns");
        map.put("健身步道", "fitnessTrails");
        map.put("主要道路材质", "mainRoadMaterial");
        map.put("供水主管线改造", "mainWaterSupply");
        map.put("供水支管线改造", "pipeWaterSupply");
        map.put("供电主线改造", "powerSupplyLine");
        map.put("供电支线改造", "powerSupplyBranch");
        map.put("供热主线改造", "heatingMainLine");
        map.put("供热支线改造", "heatingFeeder");
        map.put("燃气主线改造", "gasLines");
        map.put("燃气支线改造", "gasFeeder");
        map.put("排水主线改造", "mainDrainage");
        map.put("排水支线改造", "branchDrainage");
        map.put("路灯", "street");
        map.put("公告景观照明", "announcementLandscapeLighting");
        map.put("垃圾收集设施", "refuseCollectionFacility");
        map.put("垃圾分类", "garbageSorting");
        map.put("废品收购点", "scrapPoint");
        map.put("公共厕所", "publicToilet");
        map.put("通信基站", "communicationStation");
        map.put("通信管道", "communicationChannel");
        map.put("通信设施用房", "communicationFacilities");
        map.put("光纤入户", "opticalFiber");
        map.put("消防通道", "fireEngineAccess");
        map.put("消防设施", "fireFightingDevice");
        map.put("门禁系统", "doorSystem");
        map.put("安防监控系统", "sucurityMonitorSystem");
        map.put("防雷设施", "thunderFacilities");
        map.put("有无应急避难场地", "emergencyShelter");
        map.put("有无应急避难建筑", "emergencyShelterBuilding");
        map.put("室外活动广场", "outdoorPlaza");
        map.put("室外健身设施", "outdoorFitnessFacilities");
        map.put("儿童活动设施", "childActivityFacilities");
        map.put("室外休息设施", "outdoorRestingFacilities");
        map.put("宣传栏", "bulletinBoard");
        map.put("建筑出入口无障碍坡道", "entranceWheelchairRamp");
        map.put("户外场地无障碍坡道", "outdoorWheelchairRamp");
        map.put("社区服务用房", "communityServices");
        map.put("信报箱", "newCabinet");
        map.put("物业管理用房", "propertyManagementOccupancy");
        map.put("快递收发设施", "expressDeliveryFacilities");
        map.put("老年人服务用房", "elderlyService");
        map.put("充电桩", "chargingPile");
        map.put("外墙保温改造", "wallModificationTime");
        map.put("外墙保温改造1", "renovationBuildingNumber");
        map.put("外立面改造", "reconstructionFacadeTime");
        map.put("外立面改造1", "reconstructionFacadeNumber");
        map.put("楼道管线改造", "pipelineRenovationTime");
        map.put("楼道管线改造1", "pipelineRenovationNumber");
        map.put("水电气出户改造", "waterElectricityTime");
        map.put("水电气出户改造1", "waterElectricityNumber");
        map.put("楼道照明改造", "lightingRenovationTime");
        map.put("楼道照明改造1", "lightingRenovationNumber");
        map.put("楼体散水改造", "scatteredWaterTime");
        map.put("楼体散水改造1", "scatteredWaterNumber");
        map.put("楼道门窗改造", "doorWindowsTime");
        map.put("楼道门窗改造1", "doorWindowsNumber");
        map.put("有无加装电梯", "whetherElevatorInstalled");
        map.put("楼梯抗震加固", "seismicReinforcementStairs");

        fileName = "小区_" + System.currentTimeMillis();

        Workbook workbook = new Export2Util().getExcelByTemplate(resultData, map, "importAndExport", "小区导出模板.xlsx", 3);
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        downloadUtil.download(bos, response, URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        // 关闭
        workbook.close();

    }

    /**
     * 公租房--导出实现公租房数据的导出
     * @param accessToken           权限鉴定
     * @param response              响应
     * @param pageNumber           当前页
     * @param pageSize             页数
     * @param residenceCommunityId 小区id
     * @param buildingNum          楼栋
     * @param unit                 单元
     * @param roomNumber           房号
     * @param districtId           区
     * @param bindStatus           产权人绑定状态:0未绑定 1绑定
     * @param matchStatus          配租状态：0未配租 1可配租 2已配租
     * @param leaseStatus          出租状态：0未出租 1已出租
     * @param pwdLockStatus        密码锁授权：0未授权 1已授权 4已冻结
     * @param houseNature          性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
     * @param checkStatus          核查状态：0未核查、1已核查
     * @param orgId                组织id
     * @param search               搜索条件
     * @param houseId              房屋id
     * @param floor                楼层
     * @param area                 面积
     * @param decorateType         装修
     * @param provinceId           省
     * @param cityId               市
     * @param streetId             街道
     * @param communityId          社区
     * @param address              地址
     * @param auditStatus          房源审核状态:0未审核 1已审核
     * @param room                 室
     * @param hall                 厅
     * @param toilet               卫
     * @param houseIdList          房屋id集合
     * @throws Exception
     */
    public void houseExport(String accessToken, Integer pageNumber, Integer pageSize,
                            String residenceCommunityId, Long buildingNum, Long unit, String roomNumber, Long districtId,
                            Integer bindStatus, Integer matchStatus, Integer leaseStatus, Integer pwdLockStatus,
                            Integer houseNature, Integer checkStatus, Long orgId, String search,
                            String houseId, Integer floor, BigDecimal area, Integer decorateType, Long provinceId,
                            Long cityId, Long streetId, Long communityId, String address, Integer auditStatus,
                            Integer room, Integer hall, Integer toilet, List<String> houseIdList, HttpServletResponse response) throws Exception {
        log.info("communityService  houseExport  accessToken={} ", accessToken);

        // 获取总数
        pageSize = 10000;
        List<HouseVO> data = new ArrayList<>();
        // 表格头
        Map<String, String> map = new LinkedHashMap<>();
        // 文件名
        String fileName = "";

        // 分批查询数据, 每次查询1000条
        for (pageNumber = 0; ; pageNumber++) {
            //查询公租房数据
            HouseApiData<HouseVO> apiData = houseService.houseList(accessToken, pageNumber, pageSize, residenceCommunityId, buildingNum, unit, roomNumber,
                    districtId, bindStatus, matchStatus, leaseStatus, pwdLockStatus, houseNature, checkStatus, orgId, search,
                    houseId, floor, area, decorateType, provinceId, cityId, streetId, communityId, address, auditStatus, room, hall, toilet, houseIdList).getResultData();
            //获取公租房数据
            List<HouseVO> pageData = apiData.getData();
            if (pageData.isEmpty() || pageNumber >= 200) {
                break;
            }
            data.addAll(pageData);
        }
        //遍历公租房数据,并进行拼接存储
        for (HouseVO h : data) {
            String temp = h.getRoom() + "室" + h.getHall() + "厅" + h.getToilet() + "卫";
            h.setHouseType(temp);

            switch (h.getBuildType()) {    // 下拉选择： 居民住宅/商业/商住两用 --> 1,2,3
                case 1:
                    h.setBuildTypeName("居民住宅");
                    break;
                case 2:
                    h.setBuildTypeName("商业");
                    break;
                case 3:
                    h.setBuildTypeName("商住两用");
                    break;
            }

        }
        //创建导出模板
        map.put("省", "province");
        map.put("市", "city");
        map.put("区", "district");
        map.put("街道", "street");
        map.put("社区", "community");
        map.put("小区", "residenceCommunityName");
        map.put("详细地址", "address");
        map.put("楼栋（栋）", "buildingNumName");
        map.put("单元（单元）", "unitName");
        map.put("楼层", "floor");
        map.put("房号", "roomNumber");
        map.put("面积", "area");
        map.put("户型", "houseType");
        map.put("产权所有人", "orgName");
        map.put("物业公司", "houseProperty");
        map.put("总层数", "houseNumber");
        map.put("楼宇类型", "buildTypeName");

        fileName = "公租房_" + System.currentTimeMillis();
        //使用Export2Util工具进行导出,文件格式为.xlsx
        Workbook workbook = new Export2Util().getExcelByTemplate(data, map, "importAndExport", "公租房导出模板.xlsx", 3);
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        downloadUtil.download(bos, response, URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        // 关闭
        workbook.close();

    }


        public void communityHouseExport(String accessToken, HttpServletResponse response, HouseVO vo, List<String> houseIdList) throws Exception {
            log.info("communityService  communityHouseExport  accessToken={} ", accessToken);

        // 获取总数
        Integer pageSize = 10000;
        List<HouseVO> data = new ArrayList<>();
        // 表格头
        Map<String, String> map = new LinkedHashMap<>();
        // 文件名
        String fileName = "";

        // 分批查询数据, 每次查询1000条
            for (int pageNumber = 0; ; pageNumber++) {
                ApiData<HouseVO> apiData = appCommunityHouseService.pageslist(accessToken, pageNumber, pageSize, vo, houseIdList).getResultData();

                List<HouseVO> pageData = apiData.getData();
                if (pageData.isEmpty() || pageNumber >= 200) {
                    break;
                }
                data.addAll(pageData);
            }

        List<HouseVO> resultdata = new ArrayList<>();//房产
        List<HouseVO> housedata = new ArrayList<>();//公租房
        for (HouseVO h : data) {
            String temp = h.getRoom() + "室" + h.getHall() + "厅" + h.getToilet() + "卫";
            h.setHouseType(temp);

            switch (h.getHouseNature()) {   //Integer houseNature; //性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
                case 2:
                    h.setHouseNatureName("廉租房");
                    break;
                case 3:
                    h.setHouseNatureName("商品房");
                    break;
                case 4:
                    h.setHouseNatureName("经济适用房");
                    break;
                case 5:
                    h.setHouseNatureName("其它");
                    break;
            }

            switch (h.getBuildType()) {    // 下拉选择： 居民住宅/商业/商住两用 --> 1,2,3
                case 1:
                    h.setBuildTypeName("居民住宅");
                    break;
                case 2:
                    h.setBuildTypeName("商业");
                    break;
                case 3:
                    h.setBuildTypeName("商住两用");
                    break;
            }

            Date startTime = h.getStartTime();
            if (startTime != null) {
                h.setChargeStartTime(DateUtils.format(startTime, DateUtils.FORMAT_DATE_8));
            }

            if (!h.getHouseNature().equals(1)) {
                resultdata.add(h);
            } else {
                housedata.add(h);
            }

        }

        map.put("省", "province");
        map.put("市", "city");
        map.put("区", "district");
        map.put("街道", "street");
        map.put("社区", "community");
        map.put("小区", "residenceCommunityName");
        map.put("详细地址", "address");
        map.put("楼栋（栋）", "buildingNumName");
        map.put("单元（单元）", "unitName");
        map.put("楼层", "floor");
        map.put("房号", "roomNumber");
        map.put("面积", "area");
        map.put("户型", "houseType");
        map.put("性质", "houseNatureName");
        map.put("水表号", "waterMeter");
        map.put("电表号", "electricMeter");
        map.put("燃气表号", "gasMeter");
        map.put("产权所有人姓名", "ownerName");
        map.put("产权所有人电话", "ownerMobile");
        map.put("收费开始日期", "chargeStartTime");
        map.put("物业公司", "houseProperty");
        map.put("总层数", "houseNumber");
        map.put("楼宇类型", "buildTypeName");

        fileName = "非公租房_" + System.currentTimeMillis();
        Workbook workbook = new Export2Util().getExcelByTemplate(resultdata, map, "importAndExport", "非公租房导出模板.xlsx", 3);

        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        // response.setContentType("application/octet-stream;charset=utf-8");
        // ServletOutputStream outputstream = response.getOutputStream();//取得输出流

        // File file = file(workbook, fileName);

        //公租房数据
        if (housedata.size() > 0) {

            Map<String, String> m = new LinkedHashMap<>();
            m.put("省", "province");
            m.put("市", "city");
            m.put("区", "district");
            m.put("街道", "street");
            m.put("社区", "community");
            m.put("小区", "residenceCommunityName");
            m.put("详细地址", "address");
            m.put("楼栋（栋）", "buildingNumName");
            m.put("单元（单元）", "unitName");
            m.put("楼层", "floor");
            m.put("房号", "roomNumber");
            m.put("面积", "area");
            m.put("户型", "houseType");
            m.put("产权所有人", "orgName");
            m.put("物业公司", "houseProperty");
            m.put("总层数", "houseNumber");
            m.put("楼宇类型", "buildTypeName");

            String fName = "公租房_" + System.currentTimeMillis();
            Workbook wb = new Export2Util().getExcelByTemplate(housedata, m, "importAndExport", "公租房导出模板.xlsx", 3);

            // File file1 = file(wb, fName);

            // File[] srcFiles = {file, file1};
            // File zipFile = new File(contractTmpDir + "ZipFile.zip");
            // zipFiles(srcFiles, zipFile);
            // byte[] zip = File2byte(zipFile);
            // outputstream.write(zip);
            // outputstream.flush();//刷数据
            // wb.close();
            // workbook.close();
            DownloadUtil downloadUtil = new DownloadUtil();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wb.write(bos);
            downloadUtil.download(bos,response,URLEncoder.encode(fName + ".xlsx", "utf-8"));
            wb.close();
            return;
        }

        // File[] srcFiles = {file};
        // File zipFile = new File(contractTmpDir + "ZipFile.zip");
        // zipFiles(srcFiles, zipFile);
        // byte[] zip = File2byte(zipFile);
        // outputstream.write(zip);
        // outputstream.flush();//刷数据
        // // 关闭
        // workbook.close();
            DownloadUtil downloadUtil = new DownloadUtil();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            downloadUtil.download(bos,response,URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            workbook.close();

        }

    /**
     * 将文件转换成byte数组
     */
    public static byte[] File2byte(File tradeFile) {
        byte[] buffer = null;
        try {
            FileInputStream fis = new FileInputStream(tradeFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            byte[] b = new byte[1024];
            int n;
            while ((n = fis.read(b)) != -1) {
                bos.write(b, 0, n);
            }
            fis.close();
            bos.close();
            buffer = bos.toByteArray();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return buffer;
    }

    public File file(Workbook workbook, String fileName) throws IOException {

        // 生成文件
        File file = new File(contractTmpDir + fileName + ".xlsx");
        OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
        try {
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook.write(out);

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return file;

    }

    public static void zipFiles(File[] srcFiles, File zipFile) {
        // 判断压缩后的文件存在不，不存在则创建
        if (!zipFile.exists()) {
            try {
                zipFile.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        // 创建 FileOutputStream 对象
        FileOutputStream fileOutputStream = null;
        // 创建 ZipOutputStream
        ZipOutputStream zipOutputStream = null;
        // 创建 FileInputStream 对象
        FileInputStream fileInputStream = null;

        try {
            // 实例化 FileOutputStream 对象
            fileOutputStream = new FileOutputStream(zipFile);
            // 实例化 ZipOutputStream 对象
            zipOutputStream = new ZipOutputStream(fileOutputStream);
            // 创建 ZipEntry 对象
            ZipEntry zipEntry = null;
            // 遍历源文件数组
            for (int i = 0; i < srcFiles.length; i++) {
                // 将源文件数组中的当前文件读入 FileInputStream 流中
                fileInputStream = new FileInputStream(srcFiles[i]);
                // 实例化 ZipEntry 对象，源文件数组中的当前文件
                zipEntry = new ZipEntry(srcFiles[i].getName());
                zipOutputStream.putNextEntry(zipEntry);
                // 该变量记录每次真正读的字节个数
                int len;
                // 定义每次读取的字节数组
                byte[] buffer = new byte[1024];
                while ((len = fileInputStream.read(buffer)) > 0) {
                    zipOutputStream.write(buffer, 0, len);
                }
            }
            zipOutputStream.closeEntry();
            zipOutputStream.close();
            fileInputStream.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 公租房房源录入模板下载,实现了用户对公租房房源录入模板下载
     * @param accessToken 权限鉴定
     */
    public void houseDownload(String accessToken) {
        try {
            // 读到流中
            InputStream in = this.getClass().getClassLoader().getResourceAsStream("static/exceltemplates/importAndExport/公租房房源录入模板.xlsx");
            //把模板数据响应给前端给用户
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

            // 导出文件下载
            DownloadUtil downloadUtil = new DownloadUtil();
            // 内存输出流(将数据写到内存)
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            // 读取输入流数据后写到内存中
            byte[] b = new byte[1024];
            int len;
            try {
                while ((len = in.read(b)) > 0) {
                    bos.write(b, 0, len);
                }

                in.close();
            } catch (IOException e) {
                //如果用异常记录异常信息
                log.error("Download the document is failed,message:{}", e.getMessage());
            }
            // 导出文件下载
            downloadUtil.download(bos, response, "公租房房源录入模板.xlsx");
        } catch (IOException e) {
            //如果用异常记录异常信息
            log.error(e.getMessage(), e);
        }
    }

    public void communityHouseDownload(String accessToken) {
        try {
            // 读到流中
            InputStream in = this.getClass().getClassLoader().getResourceAsStream("static/exceltemplates/importAndExport/非公租房录入模板.xlsx");

            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

            // 导出文件下载
            DownloadUtil downloadUtil = new DownloadUtil();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            byte[] b = new byte[1024];
            int len;
            try {
                while ((len = in.read(b)) > 0) {
                    bos.write(b, 0, len);
                }

                in.close();
            } catch (IOException e) {
                log.error("Download the document is failed,message:{}", e.getMessage());
            }

            downloadUtil.download(bos, response, "非公租房录入模板.xlsx");
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

    public void communityDownload(String accessToken) {
        try {
            // 读到流中
            InputStream in = this.getClass().getClassLoader().getResourceAsStream("static/exceltemplates/importAndExport/小区导入.xlsx");

            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

            // 导出文件下载
            DownloadUtil downloadUtil = new DownloadUtil();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            byte[] b = new byte[1024];
            int len;
            try {
                while ((len = in.read(b)) > 0) {
                    bos.write(b, 0, len);
                }

                in.close();
            } catch (IOException e) {
                log.error("Download the document is failed,message:{}", e.getMessage());
            }

            downloadUtil.download(bos, response, "小区导入.xlsx");
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 查询房屋类型
     *
     */
    public ResultBean<Object> findHouseType(String accessToken) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //新增不要展示公租房属性 20230309 xiaolong
            List<HouseType> all = houseTypeRepository.findAll();
            all.remove(0);
            return ResultBean.successfulResult(all);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultBean.failedResultOfException();
        }
    }
}
